Link to home
Start Free TrialLog in
Avatar of DBL9SSG
DBL9SSGFlag for United States of America

asked on

Exporting SSIS Package Programmatically

Hello,
I'm trying to create copies of all ssis packages in MSDB folder and copy them to a directory in windows.   I'm currently using the SaveToXML method to copy the ssis package, but I'm getting the following error:

Failed to apply package protection with error 0xC0014061 "The protection level, ServerStorage, cannot be used when saving to this destination. The system could not verify that the destination supports secure storage capability.". This error occurs when saving to Xml.


The code I'm using is below.  


Imports System
Imports System.Text
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask




Public Class ScriptMain
    Public Sub Main()
        Dim sqlFolder As String
        Dim sqlServer As String

        Dim ssisApplication As Application
        Dim sqlPackages As PackageInfos
        Dim sqlPackage As PackageInfo
        Dim Packg As New Package
        Dim NewFileName As String
        Dim SSISName As String


        sqlServer = "SERVERNAME"

        ssisApplication = New Application()

        ' Get packages stored in MSDB.
        sqlFolder = "MSDB/CSRT_SSIS_CONVERSION"

        sqlPackages = ssisApplication.GetDtsServerPackageInfos(sqlFolder, sqlServer)
        If sqlPackages.Count > 0 Then
            Dts.Events.FireInformation(0, "Packages stored in " & sqlFolder & ":", Nothing, String.Empty, 0, False)
            For Each sqlPackage In sqlPackages
                Console.WriteLine(sqlPackage.Name)
                Dts.Events.FireInformation(0, sqlPackage.Name, Nothing, String.Empty, 0, False)

                NewFileName = "D:\SSISSaveFiles\" & sqlPackage.Name & ".dtxs"
                SSISName = sqlFolder & "\" & sqlPackage.Name

                Packg = ssisApplication.LoadFromDtsServer(SSISName, sqlServer, Nothing)

                Packg.SaveToXML(NewFileName, Nothing)
            Next
        End If

        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class




Thank you very much.
Avatar of kenwagers
kenwagers
Flag of United States of America image

I would suggest saving the files as dtsx - this makes them much more portable between servers.
Avatar of DBL9SSG

ASKER

KenWagers,
I appreciate your response, although the suggestion was not the solution, it gave me the oppurtinity to fix the file extension.  

Here's the change that saves the ssis package:  
  Instead of   Packg.SaveToXML(NewFileName, Nothing).. I'm using
   ssisApplication.SaveToXml(NewFileName, Packg, Nothing)



*************************************************************************************************************
            For Each sqlPackage In sqlPackages
                Console.WriteLine(sqlPackage.Name)
                Dts.Events.FireInformation(0, sqlPackage.Name, Nothing, String.Empty, 0, False)

                NewFileName = "D:\SSISSaveFiles\" & sqlPackage.Name & ".dtsx"
                SSISName = sqlFolder & "/" & sqlPackage.Name

                Packg = ssisApplication.LoadFromDtsServer(SSISName, sqlServer, Nothing)
                Packg.ProtectionLevel = DTSProtectionLevel.DontSaveSensitive
                ssisApplication.SaveToXml(NewFileName, Packg, Nothing)
            Next

*************************************************************************************************************
Avatar of DBL9SSG

ASKER

I'd like to close this question.  However, I'd like to partially award kenwagers 100 points.  

Thank you,
dbl9ssg
ASKER CERTIFIED SOLUTION
Avatar of kenwagers
kenwagers
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DBL9SSG

ASKER

Thank you