re: Deploy SSIS 2012 projects using catalog.deploy_project Stored Procedure

Carla Sabotta — Thu, 27 Dec 2012 02:50:02 GMT

Hi Koen,

Yes, you can deploy projects from server to server using the catalog.deploy_project stored procedure.

You create a Linked Server object to a SQL Server instance and configure it to enable RPC and RPC Out, and set Enable Promotion of Distributed Transaction to False (Server options page of the Linked Server Properties dialog). And, be sure to enable Dynamic Parameters for the provider selected for the Linked Server object; to enable it, right-click the provider under the Providers node (under Linked Servers in Object Explorer), and then click Properties.

Call catalog.get_project to return the binary for the project and then call catalog.deploy_project. In the following example, catalog.get_project returns a binary for the MySSISProject project on the linked server. The value is inserted into a table variable of type varbinary(max); the linked server can't return results that are varbinary(max). The catalog.deploy_project deploys the project to the local server, to the folder named DestFolder.

declare @resultsTableVar table (

project_binary varbinary(max)

)

-- Insert the results into table variable

-- Linked server cannot return results that are varbinary(max)

-- To do the steps below, Enable RPC/RPC-out for the linked server

INSERT @resultsTableVar (project_binary)

EXECUTE [MyLinkedServer].[SSISDB].[catalog].[get_project] 'MyFolder','MySSISProject'

declare @project_binary varbinary(max)

select @project_binary = project_binary from @resultsTableVar

-- Deploy to SSIS Catalog

exec [SSISDB].[CATALOG].[deploy_project] 'DestFolder','MySSISProject',@project_binary

re: Deploy SSIS 2012 projects using catalog.deploy_project Stored Procedure

Koen Verbeeck — Mon, 10 Dec 2012 07:06:53 GMT

I'm guessing this can be used as well to deploy projects from server to server? I'll have to test it out, but that would be awesome.

re: Deploy SSIS 2012 projects using catalog.deploy_project Stored Procedure

ArthurZ — Thu, 06 Dec 2012 20:49:48 GMT

I think this a very useful example on how to automate (or script) SSIS project deployments; also this is suited to using this approach together/within the SSDT for activities as scripting tables, data, etc. to promote a project as one monolithic unit.

Just another reason to preach to upgrades to SSIS 2012.