How to I add SSIS Packages to the MSDB database on SQL Server 2005?

Question

I have SQL Server 2005 install on my PC and am having execution problems with an SSIS Package stored in the filesystem. I want to stored this in the MSDB database but don't know how to do this.

I watched this video: http://download.microsoft.com/download/5/3/0/5303799D-8739-4BDC-8062-337368357391/SQLServerAgentTroubleshoot.wmv

This video guides you through the process in SQL Management Studio, however it shows Integration Services as a separate section in Management Studio and I don't have this?

Any ideas how I can see Intergration Service in Management Studio or how I can add the package to the MSDB database without it?

Answer 1

In the Object Explorer in Management Studio, the Connect button has a drop down that allows you to specify Integration Services. You can use this to get to your MSDB package repository.

However, to get your SSIS package in there, the easiest way is to build it for deployment so you get a .manifest file. Run this and you can choose to install it to MSDB.

Answer 2

Alternatively, if you built your SSIS package in BIDS you can go to the menu, choose "File > Save Copy of PACKAGENAME.dtsx as ...", choose "SQL Server" as the destination and fill in the server name. You'll also get to choose the protection level of the package regarding sensitive data, you can view all of your options at MSDN.

Answer 3

These answers make me angry. They are clicky-clicky UI workflows and assume folks have one or two packages to deploy, not hundreds across many disparate networks.

I need a way to programatically load a dtsx package in to the /Maintenance Plan/ MSDB store. dtexec has been as close as i've gotten but it's still not working right. Any help?