THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

SSIS Package Builds

So you have written an SSIS package and now its time to package it up and send it off to your QA department. Excellent; you need to use the build function in Business Intelligence Development Studio. On the surface this is pretty simply but there are a few things to understand. You can’t just right click your project and select Build, or even use the Build Solution menu option. Before you can do either of these things, you need to adjust your project’s build settings. To do that, right-click the project in Solution Explorer, select Properties, and then select Deployment Utility. This should have you looking at the follow dialog.

clip_image002

Notice that the CreateDeploymentUtility option is set to False. This must be set to true before you can build your project. The other setting of interest is DeploymentOutputPath which controls where the build will be written. Set CreateDeploymentUtility to True and click OK. Now you can right-click the project in Solution Explorer and select Build. This will write several files out to your deployment directory. The number of files will vary based on your project; in the case of my test project three files were written.

1. Package.dtsx – The actual SSIS package that was developed. You should have one DTSX file for each package in your solution.

2. Config.dtsConfig – This is the XML configuration file for my package. You could have several of these, or none, depending on whether your packages uses them

3. Test.SSISDeploymentManifest – This is the Deployment Manifest file. You will have one of these per project. This file defines all the pieces of your project and is what you use to deploy the SSIS package to a server.

Now that you have built your package, you can send all the files in your deployment directory to another person or group so that your package can be installed on an SSIS server. That process is simple; just double-click the SSISDeploymentManifest file on a machine that has the SQL Server client tools installed and this will launch the Package Installation Wizard, shown below.

clip_image004

This is a pretty simple wizard, but let’s quickly run through it. First you specify whether you want a File System Deployment or a SQL Server deployment. For this example I will choose SQL Server. Next you choose the location for the package, in our case the SQL Server and the path in MSDB. You can also choose to Rely on Server Storage for Encryption if you want sensitive information that was encrypted in the package to remain after the package has been deployed. Last, you must specify the directory on the server where you want dependencies files, such as configuration files, to be stored. If you have configurations, you will also be allowed to modify the configuration values during the wizard. Keep in mind these can also be modified later.

That’s it, click Finish and the package will be installed on your SQL Server where it can be scheduled and run.

Published Tuesday, December 08, 2009 2:29 PM by ejohnson2010
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Firmbyte said:

Eric,

This is incorrect: "Notice that the CreateDeploymentUtility option is set to False. This must be set to true before you can build your project". The option does not need to be set to True before you can build you project. You can build your project with it set to False.

Firmbyte

December 9, 2009 3:10 AM
 

ejohnson2010 said:

Let me re-phrase. This must be set to true so that when you build, the deployment utility is created.

January 6, 2010 7:25 PM
 

Monica said:

How can u clear the source and dest server and db when building the package to allow installers to set their own?

February 12, 2013 9:32 PM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement