Deploying SSIS Packages SQL Server 2008

Bit old school, having been spoilt by SQL Server 2012 and the changes to SSIS that brought with it. Deploying stuff in Business Intelligence Design Studio (BIDS) is less user friendly than I recall. Had to deploy about 20 SSIS packages to the SSIS instance, and the import dialog using SSMS is OK for one or two packages, but i wanted to do them all with out repetition. So it was time for old school command line stuff using DTUTIL, which is still around even in the coming SQL Server 2016.
for %I in (*.dtsx) do dtutil /FILE “%I” /COPY SQL;”/SSIS_Folder/%~nI” /DESTSERVER localhost
In this example i’ve already created a folder in the localhost/Stored Packages/MSDB location called ‘SSIS_Folder’, and running a cmd window from the SSIS Package location. Lets break it down:
This bit just loops though all items in the folder that have a ‘.dtsx’ extension, good old ‘for’. ‘in’ & ‘do’. ‘%I’ is just the variable that holds the file name.
for %I in (*.dtsx) do
The next bit is the good stuff, this calls dtutil and copies the package using the variable ‘%I’ to the folder SSIS_Folder to the destination server ‘localhost’
dtutil /FILE “%I” /COPY SQL;”/SSIS_Folder/%~nI” /DESTSERVER localhost
Nice and easy, completes in seconds. If you need to deploy the same files again you can run the command again and it will prompt you to overwrite with a Y/N prompt, to suppress this prompt and automatically overwrite the package add ‘/Q’ (For Quiet) at the end of the command.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s