Home
%3CLINGO-SUB%20id%3D%22lingo-sub-388110%22%20slang%3D%22en-US%22%3EDeploy%20SSIS%202012%20projects%20using%20catalog.deploy_project%20Stored%20Procedure%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388110%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Dec%2006%2C%202012%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EIn%20SQL%20Server%202012%2C%20you%20can%20use%20the%20catalog.deploy_project%20stored%20procedure%20to%20deploy%20an%20SSIS%20project%20to%20the%20SSIS%20server.%20You%20need%20to%20provide%20the%20binary%20contents%20of%20the%20project%20deployment%20file%20(.ispac%20extension)%2C%20for%20the%20%40project_stream%20parameter%2C%20along%20with%20the%20project%20name%20and%20the%20folder%20the%20project%20will%20be%20deployed%20to.%20The%20%40project_stream%26nbsp%3Bparameter%20is%20varbinary(MAX).%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EYou%20can%20retrieve%20the%20binary%20contents%20of%20the%20project%20file%20by%20using%20a%20SELECT%20statement%20with%20the%20OPENROWSET%20function%20and%20the%20BULK%20rowset%20provider.%20The%20provider%20enables%20you%20to%20read%20data%20from%20the%20file%2C%20returning%20the%20contents%20as%20a%20single-row%2C%20single-column%20rowset%20of%20type%20varbinary(max).%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EIn%20the%20following%20example%2C%20the%20SSISPackages_ProjectDeployment%20project%20is%20deployed%20to%20the%20SSIS%20Packages%20folder%20on%20the%20SSIS%20server.%20The%20binary%20data%20is%20read%20from%20the%20project%20file%20(SSISPackage_ProjectDeployment.ispac)%20and%20is%20stored%20in%20the%20%40ProjectBinary%20parameter%20of%20type%20varbinary(max).%20The%20%40ProjectBinary%20parameter%20value%20is%20assigned%20to%20the%20%40project_stream%20parameter.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EDECLARE%20%40ProjectBinary%20as%20varbinary(max)%20%3CBR%20%2F%3E%20DECLARE%20%40operation_id%20as%20bigint%20%3CBR%20%2F%3E%20Set%20%40ProjectBinary%20%3D%20(SELECT%20*%20FROM%20OPENROWSET(BULK%20'C%3A%5CMyProjects%5C%20SSISPackage_ProjectDeployment.ispac'%2C%20SINGLE_BLOB)%20as%20BinaryData)%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EExec%20catalog.deploy_project%20%40folder_name%20%3D%20'SSIS%20Packages'%2C%20%40project_name%20%3D%20'DeployViaStoredProc_SSIS'%2C%20%40Project_Stream%20%3D%20%40ProjectBinary%2C%20%40operation_id%20%3D%20%40operation_id%20out%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3C%2FP%3E%3CBR%20%2F%3E%3CP%3EFor%20instructions%20and%20examples%20on%20how%20to%20use%20the%20catalog.create_execution%2C%20catalog.set_execution_parameter_value%2C%20and%20catalog.start_execution%20stored%20procedures%20to%20create%2C%20configure%20and%20start%20a%20package%20execution%2C%20see%20this%20topic%20in%20BOL.%3C%2FP%3E%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fjj820152.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3E%20Deploy%20and%20Execute%20SSIS%20Packages%20using%20Stored%20Procedures%20%3C%2FA%3E%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-388110%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Dec%2006%2C%202012%20In%20SQL%20Server%202012%2C%20you%20can%20use%20the%20catalog.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-388110%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Esql2012%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Essis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Not applicable
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