First published on MSDN on Dec 06, 2012
In SQL Server 2012, you can use the catalog.deploy_project stored procedure to deploy an SSIS project to the SSIS server. You need to provide the binary contents of the project deployment file (.ispac extension), for the @project_stream parameter, along with the project name and the folder the project will be deployed to. The @project_stream parameter is varbinary(MAX).
You can retrieve the binary contents of the project file by using a SELECT statement with the OPENROWSET function and the BULK rowset provider. The provider enables you to read data from the file, returning the contents as a single-row, single-column rowset of type varbinary(max).
In the following example, the SSISPackages_ProjectDeployment project is deployed to the SSIS Packages folder on the SSIS server. The binary data is read from the project file (SSISPackage_ProjectDeployment.ispac) and is stored in the @ProjectBinary parameter of type varbinary(max). The @ProjectBinary parameter value is assigned to the @project_stream parameter.
DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'C:\MyProjects\ SSISPackage_ProjectDeployment.ispac', SINGLE_BLOB) as BinaryData)
Exec catalog.deploy_project @folder_name = 'SSIS Packages', @project_name = 'DeployViaStoredProc_SSIS', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out
For instructions and examples on how to use the catalog.create_execution, catalog.set_execution_parameter_value, and catalog.start_execution stored procedures to create, configure and start a package execution, see this topic in BOL.
Deploy and Execute SSIS Packages using Stored Procedures
