Congratulations, you’ve made a working SSIS package! Now what? Well, you could just sit back and bask in your success, but that might not look so good on your quarterly performance evaluation. My suggestion would be to deploy it to your production environment so it can do what it does best. “But Ryan, how does one get their package onto the production server?” I’m glad you asked. That is the topic of today’s blog.
First, you’re going to take your package. I have here a package that takes data from a flat file source, cleanses it to make sure I have all the fields I need, then loads the good data into one table and the bad data into another. Your first step is going to be to go to the properties of the project and set the “CreateDeploymentUtility” property to True. Also, note the “AllowConfigurationChanges” property. This allows the user to allow or not allow changes to any settings that may be subject to a config file at installation time.
Next, go to the build menu at the menu bar for BIDS, select “Build [ProjectName].” This will build all packages in your project. If there are any issues with any of the packages here, BIDS will let you know.
A good practice to get into is to go back into the project’s properties and reset “CreateDeploymentUtility” to False, that way when you press play on the package in the future, it won’t create the utility each time. That could be very time intensive for a larger project.
At this point, you have a deployment file in the \bin\deployment directory, so send the contents of that folder to the installation person. They will then take the contents of the folder and copy them to the server he or she wishes to deploy to. Then, double click the .SSISDeploymenManifest file (this will run the package installation wizard) and follow the prompts to deploy the project to your preferred destination, which is either as a File System Deployment or as a SQL Server Deployment. There are definite benefits for both, and some things that one does better than the other. For example, security for your package is strongest in deploying to your msdb database, but backup and recovery and troubleshooting are easier in a file system deployment. For the purposes of this subject, I chose a file system deployment.
That’s all there is to it. You can now secure your package through the SSIS Package Store and execute it from Management Studio or from the command line.