DataGinger.com

Though there are several ways for importing SSIS packages into SQL Server either by creating a stored procedure or using a SSIS package itself, I find this simple code of line much easier and handy to work with.  It utilizes the “dtutil.exe” application which is installed by default with SQL Server installation. It is capable of importing and exporting packages and is found in the Binn folder of SQL Server (For eg: C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtutil.exe)

Below is a post from Suresh, which describes how dtutil works for importing and exporting one package at a time.

How to Copy or Export an SSIS Package Using Command Prompt Utility – DTUTIL

In this article we will see deploying multiple SSIS packages.

Demo for deploying multiple packages.

1. Open command prompt where the packages (.dtsx) are stored

Tip: Hold ‘Shift’ and right click to open command prompt window at the desired location.

2. Execute the dtutil script from cmd.

for %I in (*.dtsx) do dtutil /FILE "%I" /COPY SQL;"/Packages/%~nI" /DESTSERVER localhost

Note to make the below changed before you run the above script.

• Change the package folder location where the SSIS packages will be deployed
• Change the server name at the end of the code to reflect the destination server. We can choose ‘localhost’ if running this within the server as shown in the below figure.

As seen in the above figure all our packages are imported into the server.

For more dtutil Utility options please see the MSDN article HERE.

Read Full Post »

The command prompt utility dtutil can be very handy when we want to quickly export an SSIS package from either file system to msdb or vice-versa.

For a quick demo, I created a package called “ProductPrice” uder the file system C:\packages, as shown in the screenshot below

Also, I created another package called “UpdatePrice” in SQL Server, which gets stored in msdb database, below is the screnshot

Now, let us see how we can quickly import or export these packages using cmd. For this we will be using the COPY option in the dtutil utility

• To copy package from file system to msdb

Run the below syntax from cmd:

dtutil /FILE C:\Packages\ProductPrice.dtsx /COPY SQL;ProductPrice

This copies/exports “ProductPrice” package from file system to msdb database as shown in the below screenshot

• To copy a package from msdb to file system

Run the below syntax from cmd:

dtutil /SQL UpdatePrice /COPY FILE;C:\Packages\UpdatePrice.dtsx

This copies/exports “UpdatePrice” package from msdb database to file system. Below is the screenshot

I have used “Windows Authentication” in this demo. To use mixed mode authentication or to export packages to a different server, we need to provide proper dtutil options, which can be found running the syntax dtutil /? from cmd utility.

Read Full Post »