SSIS Package Loader – MSDB Folders and Configuration Adherence

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!

  1. Launch VS.2005. Start a new VB Windows Console Application project.
  2. Add Microsoft.SQLServer.ManagedDTS library to References
  3. 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
    
  4. 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
    
  5. 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
                        
                    
                
            
        
    
    
  6. Build the console app. Copy the SSISPackageUploader.exe and SSISPackageUploader.exe.config to a new folder along with the packages you intend to upload.
  7. Modify the SSISPackageUploader.exe.config to contain the intended targetSSISserver, targetSSISMSDBfolderName, and packageManifest