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.
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
- 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.