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

I’ll be blogging some on SSIS as I now have a new gig slinging data hither and yon. Duck, you don’t want to get a DT_I8 in the face.

So, today I was bitten by the IsBig flag on DataFlow Aggregate Transformation. This flag is notated in the BOL:

A column may contain numeric values that require special consideration because of their large value or precision requirements. The Aggregation transformation includes the IsBig property, which you can set on output columns to invoke special handling of big or high-precision numbers. If a column value may exceed 4 billion or a precision beyond a float data type is required, IsBig should be set to 1.

So, the result here is a column that is Input to the Aggregate Transformation as DT_I4 and has IsBig = 1 WILL be Output DT_I8, and the IDE will not allow the otuput datatype to be changed with Advanced Editor!

This bit me with one column in a large Aggregate somehow getting set IsBig and messing up the downstream metadata. Several different looks at the issue were all proving dead ends.

Next time you run into a metadata mismatch, especially if its a DT_I4 or DT_R4 turning into their 8-byte counterparts, remember, you need to tap your inner IsBig and see the light!