Helpful information and examples on how to use SQL Server Integration Services.
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 bigintSet @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
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.
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.
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 (
-- 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