Author Bio

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS, Code Camps and several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

SSIS - Creating a Deployment Manifest

By knight_devin@hotmail.com in Devin Knight | 06-30-2009 2:49 PM | Categories: Filed under: SSIS, Business Intelligence

Rating: |

Discuss | 3,824 Reads | 512 Reads in Last 30 Days |8 comment(s)

Using a Deployment Manifest in SSIS allows you to deploy a set of packages to a target location using a wizard for installing your packages. The benefit to using it is the nice user interface that a wizard provides. The disadvantage of using it is it’s lack of flexibility. When using the Deployment Manifest keep in mind that it’s an all or nothing deployment, which means you cannot choose to just deploy one or two packages at a time from your SSIS project. Using the Deployment Manifest will deploy all your packages that are in your project. Creating the manifest file is a very simple process that I will walk you through.

For more information about Managing and Configuring SSIS package download this whitepaper I co-authored on the topic. http://www.pragmaticworks.com/downloads/ssiswhitepaper/downloadwhitepaper.aspx

Creating a Deployment Manifest

Find this portion with screenshots at my regular blog: http://blogs.pragmaticworks.com/devin_knight/

With the Solution Explorer open Right-Click on the project name and click Properties
This will open the Project Properties Pages
Select the Deployment Utility from the page
Change the CreateDeploymentUtility property to True

After you’ve gone through these steps the next time you build your project it will create the file (YourProjectName).SSISDeploymentManifest. This file is located in the same folder as your packages in the bin\Deployment folder.

If you run this file it will open the Package Installation Wizard that will allow you to deploy all your packages that were located in the project to a desired location.

Comments

vsankurathri said:

Deployment of ssis packages through manifest would be nice, but we cannot browse the ssis sub-folders on the target server. If we use msdb to store packages, we will be able to deploy the packages only to the parent folder corresponding to the instance.

thanks,

Vasavi Sankurathri

SQL Server / Oracle DBA

The Hartford

July 8, 2009 12:47 PM

knight_devin@hotmail.com said:

Vasavi

When you run the Package Installation Wizard and select SQL Server deployment. You will have the option to specify the package path. So if you have a subfolder you can navigate to it there. The bad part about it is that you cannot see which packages are already in the folder you only see the folder name itself.

July 8, 2009 1:03 PM

vsankurathri said:

Knight,

The option we have is to specify the package path to save installation dependencies. Not the path to deploy ssis packages. When you select 'SQL Server deployment' as deployment option in Package Installation wizard, the ssis packages will be stored in msdb database in the folder specified in MsDtsSrvr.ini.xml. If you have subfolders under that, you can't deploy the packages using the manifest.

It is possible only through 'import package' option from SSMS.

Hope this is clear. Thanks for the feedback.

Vasavi

July 8, 2009 2:11 PM

knight_devin@hotmail.com said:

Vasavi

Ahh I understand now, that makes sense. I can see that is a problem with the Deployment Manifest then.

July 8, 2009 2:57 PM

brucek-1118507 said:

80040154 -error when trying to deploy manifest file.

what can I do about it?

Thank you.

Bruce

October 22, 2009 7:40 AM

knight_devin@hotmail.com said:

Bruce

I haven't seen this problem before. At what point do you receieve the error? When you build the project to create the file or when you open it? Also if you can send me a screen shot.

October 22, 2009 11:55 AM

allen.lurey said:

Have run into a problem when using BIDS on my local desktop, and deploying DTSX, MANIFEST, DTSCONFIG file on the remote Database Server sql 2005. running the manifest, which initiates the deployment wizard is NOT updating the dtsx file with the server DTSCONFIG location. I am having to manually modify the dtsx file with the correct server location, and then importing the dstx file into msdb using IMPORT PACKAGE.

November 2, 2009 1:50 PM

knight_devin@hotmail.com said:

Sounds like you need to build the project again. I'm not sure why else it wouldn't update.

November 6, 2009 9:09 AM