Deploying an SSIS Package

Posted Wednesday, May 24, 2006 8:52 AM by C-Dog's .NET Tip of the Day

There are actually quite a few ways to deploy an SSIS package to a SQL Server. The first is to change the CreateDeploymentUtility property to true on the properties of your SSIS project. By changing this setting and then doing a built it copies files to the bin\deployment folder (by default) and creates a manifest file.

SSIS Deployment

You then copy these files over to the SQL Server and execute the manifest file on the SQL Server itself. This launches a wizard that guides you through the installation of your SSIS packages. It will first prompt you if you want to install the packages on the file system or on the SQL Server. If you choose filesystem, the dtsx files (the extension of an SSIS package) can be put pretty much anywhere and the SQL Server can use them. If you choose SQL Server, the packages will be stored in the MSDB database.

If your packages are not encrypted, you can check the option to validate the packages after installation. This is useful to verify that the package will work, but unfortunately does not work when you encrypt the package. The validation process will always return an error (even though the package installed fine).

Once you have done this once, you will notice the process kind of takes a while because it has to open every package in your project (and it will give you the error I mentioned yesterday on everyone). Luckily the process is actually really simple. Instead of rebuilding every time, simply copy the dtsx file from your project directory to the SQL Server and run the manifest file.

To speed up the installation process you can modify your manifest file or create a new one (it is just XML). Here it the format.

<DTSDeploymentManifest 
GeneratedBy="DOMAIN\user" GeneratedFromProjectName="Project Name" 
GeneratedDate="2006-05-22T14:15:43.2745185-05:00" 
AllowConfigurationChanges="true">
  <Package>MyPackage1.dtsx</Package>
  <Package>MyPackage2.dtsx</Package>
</DTSDeploymentManifest>

As you can see it is pretty simple. A few other things to note. First, remember that SSIS packages are not visible in SQL Server Management Studio by just connecting to the Database Services instance of a SQL Server. You have to choose Integration Service in the dropdownlist before you connect. Second, supposedly you can import a package using Management Studio, but everytime I try it, I get an error. Maybe it will work better in a different environment, I don't know.

Read the complete post at http://www.dotnettipoftheday.com/blog.aspx?id=279