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!
- Launch VS.2005. Start a new VB Windows Console Application project.
- Add Microsoft.SQLServer.ManagedDTS library to References
- Paste this into Module 1.vb
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
- Create a new class PackageHelper.vb and paste this code
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
- Add Settings.Settings to the project to create the XML configuration file for the installer.Name: targetSSISserver
Type: String
Value: your server nameName: targetSSISMSDBfolderName
Type: String
Value: Your MSDB Folder Name – This must pre-exist on the target SSIS ServerName: packageManifest
Type: System.Collections.Specialized.StringCollection
Value: newline-delimited list of package names. This will be an element list in the XML Config file.An example resulting app.config :CHMW003732 Deployment Test stageGAAPUploadReserves packageNotFound
- Build the console app. Copy the SSISPackageUploader.exe and SSISPackageUploader.exe.config to a new folder along with the packages you intend to upload.
- Modify the SSISPackageUploader.exe.config to contain the intended targetSSISserver, targetSSISMSDBfolderName, and packageManifest