After we had the data warehouse built in our automated build, we needed a way to automatically deploy our SSIS packages and then call them. You can use a tool called DTUtil to manipulate Integration Services on the server. You can create and remove directores in msdb storage for SSIS packages, sign packages – and lots of other things.
I didn’t find many examples of using DTUtil so I’ll post an example.
Here is the command to deploy a package to the server.
dtutil /DestS [servername] /FILE [filename].dtsx /COPY SQL;[foldername]\[filename] /QUIET
If you will notice, you need to put .dtsx in the /FILE argument but not in the /COPY argument. Also, it doesn’t matter what order the arguments are in so don’t worry about that. In the /COPY argument the SQL means that I’m copying the package to the msdb on a SQL server. The /QUIET means that it won’t complain if the package is already on the server – it will just overwrite it.
We were able to implement this right inside our NAnt script for deploying the SSIS packages. Next we need to call the package right after deploy. This might involve sp_cmdshell or calling a job out of a stored procedure. We’ll see what works best.