Here is a 1-2-3 for building your own VB.NET 2.0 Console application that will allow the upload of SSIS packages to SQL Server storage in the MSDB folder of your choice. The process also allows correct operation of XML Configurations on the packages (other configurations should work too, not tested by me in production though).
Here goes!
Module Module1
Sub Main()
Dim packages As System.Collections.Specialized.StringCollection = My.Settings.packageManifest
Dim pkgName As String
Dim fileName As String
Dim processContinue As Boolean = True
For Each pkgName In packages
Dim pkgFilePath As String = My.Application.Info.DirectoryPath + "\" + pkgName + ".dtsx"
If Not System.IO.File.Exists(pkgFilePath) Then
Console.WriteLine("Package " + pkgName + ".dtsx is not found on the local file folder.")
processContinue = False
End If
Next
For Each fileName In System.IO.Directory.GetFiles(My.Application.Info.DirectoryPath, "*.dtsx")
pkgName = System.IO.Path.GetFileNameWithoutExtension(fileName)
If Not packages.Contains(pkgName) Then
Console.WriteLine("File " + pkgName + ".dtsx is found on the local file folder but is not included in the .config
section.")
processContinue = False
End If
Next
If Not processContinue Then
Console.WriteLine("Please verify that all files in the .config
section are on the local folder.")
Console.WriteLine("Please verify that all files in the local folder are listed in the .config
section.")
Else
For Each pkgName In packages
PackageHelper.SaveToSQL(pkgName)
Next
End If
Console.Write("Press Any Key ...")
Console.Read()
End Sub
End Module
Imports Microsoft.SqlServer
Public Class PackageHelper
Private Shared Function loadSSISPackage(ByVal pkgName As String) As Dts.Runtime.Package
Dim dtsapp As Dts.Runtime.Application = New Dts.Runtime.Application
Dim pkg As Dts.Runtime.Package = dtsapp.LoadPackage(pkgName, Nothing)
Return pkg
End Function
Public Shared Sub SaveToSQL(ByVal pkgName As String)
Dim dtsapp As Dts.Runtime.Application = New Dts.Runtime.Application
Dim pkgFilePath As String = My.Application.Info.DirectoryPath + "\" + pkgName + ".dtsx"
Dim ssisFolder As String = "\\" + My.Settings.targetSSISMSDBfolderName
Dim ssisServer As String = My.Settings.targetSSISserver
Dim ssisPackagePath As String = ssisFolder + "\" + pkgName
If Not dtsapp.FolderExistsOnSqlServer(ssisFolder, ssisServer, Nothing, Nothing) Then
Console.WriteLine("Server:" + ssisServer + " Folder:" + ssisFolder + " SSIS Msdb Folder is not found on server. Check Project Settings (Settings.settings).")
ElseIf Not System.IO.File.Exists(pkgFilePath) Then
Console.WriteLine("Package: " + pkgFilePath + " The file specified is not found in the " + My.Application.Info.ProductName + " current directory.")
Else
Console.WriteLine("Saving package to SQL (" + ssisServer + ") " + ssisPackagePath + " Starting ...")
Dim pkg As Dts.Runtime.Package = PackageHelper.loadSSISPackage(pkgFilePath)
If dtsapp.ExistsOnSqlServer(ssisPackagePath, ssisServer, Nothing, Nothing) Then
Console.WriteLine("Saving package to SQL (" + ssisServer + ") " + ssisPackagePath + " Removing Previous Instance ...")
dtsapp.RemoveFromSqlServer(ssisPackagePath, ssisServer, Nothing, Nothing)
End If
Console.WriteLine("Saving package to SQL (" + ssisServer + ") " + ssisPackagePath + " ...")
dtsapp.SaveToSqlServerAs(pkg, Nothing, ssisPackagePath, ssisServer, Nothing, Nothing)
Console.WriteLine("Saving package to SQL (" + ssisServer + ") " + ssisPackagePath + " Complete")
End If
End Sub
End Class
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<sectionGroup name="userSettings" type="System.Configuration.UserSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" >
<section name="SSISPackageInstaller.My.MySettings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" allowExeDefinition="MachineToLocalUser" requirePermission="false" />
</sectionGroup>
</configSections>
<userSettings>
<SSISPackageInstaller.My.MySettings>
<setting name="targetSSISserver" serializeAs="String">
<value>CHMW003732</value>
</setting>
<setting name="targetSSISMSDBfolderName" serializeAs="String">
<value>Deployment Test</value>
</setting>
<setting name="packageManifest" serializeAs="Xml">
<value>
<ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<string>stageGAAPUploadReserves</string>
<string>packageNotFound</string>
</ArrayOfString>
</value>
</setting>
</SSISPackageInstaller.My.MySettings>
</userSettings>
</configuration>