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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
kenwagersCommented:
Glad you found the problem - appreciate the note.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DBL9SSGAuthor Commented:
Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.