One-click Deploy SSIS Package from Visual Studio to SQLServer

Visual Studio can deploy Analysis Services and Reporting Services projects to a server but there is no such option for Integration Services. Yet in my current setup I need to run packages on the server: multiple packages maintained by different people are part of a larger chain. deploymnuExecuting only one package makes no sense in this case. Sidenote: another reason that forces me to run packages on the server is because anything that has “TransactionOption = Required” fails to run from a client pc. If somebody knows of a way to make it run, please leave a comment…

Luckily there is an easy way to add the deployment capability to VS. In short: a custom action calls a batch command file that in turn uses dtutil to actually deploy the SSIS package.

1- Create a batch command file

@echo off
set DESTSERVER=yourserver
set DESTLOCATION=path
set SOURCE=%1
set DESTNAME=%~2
dtutil /FILE %SOURCE% /DestServer %DESTSERVER% /COPY SQL;%DESTLOCATION%\%DESTNAME% /Quiet

There are 2 things hard-coded into the command file: the server name and the location within MSDB. The location is something like a folder structure. Leave it empty if you want to deploy to the base of MSDB.

2- Add a menu to the External Tools menu

Go to “Tools”, then “External Tools…” and click “Add”. This will add a menu option under the menu “Tools”.
Title: Deploy to MSDB (or whatever you want to see in the menu)
Command: the full path to the batch command file created in step 1.
Arguments:$(ItemPath) $(ItemFileName)
deploytoolKeep a space between the two arguments. You’ll notice a new menu under the Tools menu. You can then go one step further and add an icon to the toolbar.

3- Add an icon for the deploy menu item in the toolbar
It is not easy to find, but you can create your custom icon and it takes only a minute or so.
- Right-click the toolbar and choose “Customize…”
- In the “Command” tab, choose in the left listbox “Tools”.
- Pick “External Command 1″ (or another number if you did not put the menu item on top in step 2) and drag it to the toolbar.
- Click “Rearrange Commands…”, tick the option “Toolbar” and select the toolbar in which you dropped the menu item (probably “Standard”).
- Select “External Command 1″ in the listbox and then click “Modify Selection”, then “Edit Button editor”.
- Make a nice little drawing and close the icon editor.deployicon
- Click “Modify Selection” once more and select “Default Style”.

4- All set!
If you click the newly added icon it will deploy the active SSIS package to the server.
Any Sql Server Agent jobs that launch that package will automatically use the newly deployed version – no need to edit the job.
There is one caveat: remember to first save the project before clicking on the deploy icon as it will deploy the dtsx in its current state on disk.

14 Comments »

Comment by Dave Lora
2008-06-30 21:21:15

This is great — THANKS!!

I made one minor modification – I put double-quotes around %DESTLOCATION%\%DESTNAME% in the batch file, in order to allow packages with spaces in their names. It seems to work fine.

Dave

 
Comment by Josh Blair
2008-10-06 17:48:57

This is great! Thanks for the instructions.

 
Comment by Dave
2008-10-14 18:03:09

Great idea – this is such an annoying oversight left out by the MS team!

There is one caveat – it doesn’t seem to work with the Sql2008 version of dtutil – I get this error: Could not find stored procedure ’sp_ssis_putpackage’.

But using the Sql2005 version of dtutil works even when copying to a 2008 server. Go figure!

 
Comment by Pav
2009-07-29 21:04:59

great post!! saved me tons of hassle. Thanks so much.

 
Comment by Dan
2009-09-25 23:47:50

This now saves me so much time… Thanks!

 
Comment by Jeff
2009-09-30 16:14:04

Very helpful. Thank you!

If you have more than one version of Business Intelligence Management Studio installed (i.e. 2005, 2008) make sure you specify the full path to ‘dtutil’ for the version you want to use. For example:

“C:\Program Files\Microsoft SQL Server\100\DTS\Binn\dtutil”

 
Comment by Jay
2010-06-01 07:08:12

Hi,

Your utility is very helpful, but I need to deploy the packages inside a folder on the server. Can you help me pls?

Thanks,
Jay

 
Comment by Tom VdP
2010-06-04 19:56:37

Hi Jay,
That is exactly what the DESTLOCATION variable in the batch file should contain!

 
Comment by Steve Rezhener
2010-10-14 01:53:42

Tom,
Kudos, this is a great addition to other deployment options. However, I’ll stick to BIDS Helper BIDS add-on (available on CodePlex, http://bidshelper.codeplex.com/) as it has more visually appealing interface and many other great features.

 
Comment by ds
2011-01-19 19:54:22

It’s works , but i have one problem, its deploy only de last package!!

 
Comment by himanshu
2013-04-18 08:39:55

but 1.if error occur how to get in log file
2.how to change config path (packageDependencie/dts.config)at run time + is there any provision for multiple config file deployment

Comment by Tom VdP
2013-05-22 07:35:42

himanshu: it is better to check the “use output window” in the configuration of the external command (step 3 in the article above).

 
 
Comment by chứng chỉ b2
2016-12-11 03:41:05

Great site. A lot of helpful info here. I’m sending it to some buddies ans also sharing in delicious.
And naturally, thank you on your sweat!

 
Name (required)
E-mail (required - never shown publicly)
URI
Your Comment (smaller size | larger size)
You may use <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> in your comment.

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Click to hear an audio file of the anti-spam word

Trackback responses to this post