How can one deploy packages programmatically? Here is the original question –
Is it possible to deploy a package programmatically? We have an application which has a work flow for approval of object. If the object (ssis package) is approved by the concerned authority it has to be deployed to sql server. How can this code be integrated into the application?
First, what does it mean to deploy a package? The package is simply a document (DTSX file). SSIS utilities (DTEXEC, DTUTIL and other) natively support three locations for such files: the actual file in the OS file system, a row in SQL table, or virtualized file in SSIS storage (which wraps the previous two). If you use custom application to run your packages, you can store them anywhere you want (e.g. application resources) – and use LoadFromXML to load it.
Since package is just a file, in most cases simply copying a file to destination directory location, or to SQL table is all it takes to deploy a package. One can use SSIS API (like SaveToSqlServer) to do this, run DTUTIL, or copy file using Win32 or .NET APIs.
In some cases it is necessary to make some adjustments to the package, e.g. if you have multiple packages calling each other – modify the parent package to correctly reference new location of child package(s). This can be done using SSIS API as well, but it is easier and usually better for maintainability to do this using SSIS configurations – edit the configuration values instead of the package itself.