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.
DBL9SSGAsked:
Who is Participating?
 
kenwagersConnect With a Mentor Commented:
Glad you found the problem - appreciate the note.
0
 
kenwagersCommented:
I would suggest saving the files as dtsx - this makes them much more portable between servers.
0
 
DBL9SSGAuthor Commented:
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

*************************************************************************************************************
0
 
DBL9SSGAuthor Commented:
I'd like to close this question.  However, I'd like to partially award kenwagers 100 points.  

Thank you,
dbl9ssg
0
 
DBL9SSGAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.