DBL9SSG
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.Ru ntime
Imports Microsoft.SqlServer.Dts.Ta sks.BulkIn sertTask
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.GetDtsServ erPackageI nfos(sqlFo lder, 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(sqlPacka ge.Name)
Dts.Events.FireInformation (0, sqlPackage.Name, Nothing, String.Empty, 0, False)
NewFileName = "D:\SSISSaveFiles\" & sqlPackage.Name & ".dtxs"
SSISName = sqlFolder & "\" & sqlPackage.Name
Packg = ssisApplication.LoadFromDt sServer(SS ISName, sqlServer, Nothing)
Packg.SaveToXML(NewFileNam e, Nothing)
Next
End If
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Thank you very much.
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.Ru
Imports Microsoft.SqlServer.Dts.Ta
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.GetDtsServ
If sqlPackages.Count > 0 Then
Dts.Events.FireInformation
For Each sqlPackage In sqlPackages
Console.WriteLine(sqlPacka
Dts.Events.FireInformation
NewFileName = "D:\SSISSaveFiles\" & sqlPackage.Name & ".dtxs"
SSISName = sqlFolder & "\" & sqlPackage.Name
Packg = ssisApplication.LoadFromDt
Packg.SaveToXML(NewFileNam
Next
End If
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Thank you very much.
I would suggest saving the files as dtsx - this makes them much more portable between servers.
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(NewFileNam e, Nothing).. I'm using
ssisApplication.SaveToXml( NewFileNam e, Packg, Nothing)
************************** ********** ********** ********** ********** ********** ********** ********** ********** ***
For Each sqlPackage In sqlPackages
Console.WriteLine(sqlPacka ge.Name)
Dts.Events.FireInformation (0, sqlPackage.Name, Nothing, String.Empty, 0, False)
NewFileName = "D:\SSISSaveFiles\" & sqlPackage.Name & ".dtsx"
SSISName = sqlFolder & "/" & sqlPackage.Name
Packg = ssisApplication.LoadFromDt sServer(SS ISName, sqlServer, Nothing)
Packg.ProtectionLevel = DTSProtectionLevel.DontSav eSensitive
ssisApplication.SaveToXml( NewFileNam e, Packg, Nothing)
Next
************************** ********** ********** ********** ********** ********** ********** ********** ********** ***
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(NewFileNam
ssisApplication.SaveToXml(
**************************
For Each sqlPackage In sqlPackages
Console.WriteLine(sqlPacka
Dts.Events.FireInformation
NewFileName = "D:\SSISSaveFiles\" & sqlPackage.Name & ".dtsx"
SSISName = sqlFolder & "/" & sqlPackage.Name
Packg = ssisApplication.LoadFromDt
Packg.ProtectionLevel = DTSProtectionLevel.DontSav
ssisApplication.SaveToXml(
Next
**************************
ASKER
I'd like to close this question. However, I'd like to partially award kenwagers 100 points.
Thank you,
dbl9ssg
Thank you,
dbl9ssg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you