When using the new SQL Server Integration Services (SSIS) catalog we can deploy our solutions using the UI or build a T-SQL script to deploy it creating folders on the way for a repeatable successful deployment package.
Firstly we set the database, SSISDB and then initialise our parameters.
— Set SSISDB
USE [SSISDB]
— Initialise parameters
DECLARE @ProjectBinary VARBINARY(MAX)
DECLARE @operation_id BIGINT
We then load the .ispac file we created earlier when exporting the project from Visual Studio. Using the openrowset http://technet.microsoft.com/en-us/library/ms190312.aspx method we can bulk load the file into our binary parameter.
— Read file into a binary data type parameter
Set @ProjectBinary = (SELECT * FROM OPENROWSET (BULK ‘C:PackageFile.ispac’, SINGLE_BLOB) AS ProjectBinary)
Next we create the folder structure within the integration service catalogs. In this example we are creating a TestDeployment folder
— Create the folder inside the catalog
EXEC catalog.create_folder ‘TestDeploymentFolder’
Finally we use the catalog.deploy_project http://technet.microsoft.com/en-us/library/ff878004.aspx method to deploy the project to the integration service catalog. Note the folder name must exist (we just created one in our example) and the VSProjectName must be the name in the Visual Studio project.
— Deploy the Project VSProjectName
EXEC catalog.deploy_project @folder_name = ‘TestDeploymentFolder’, @Project_name = ‘VSProjectName’, @Project_Stream = @ProjectBinary, @operation_id = @operation_id
OUT
If we get an error we will be directed to look at the integration service catalogs executions. Whilst this is usually helpful when building and testing your solutions it doesn’t always give the information required, as in this instance. If we want to query any errors, we canlook at the message column in the catalog.operation_messages table as below.
— Check for any errors using the returned @operation_id
SELECT * FROM catalog.operation_messages where operation_id = @operation_id
The information in the catalog.operation_messages table usually provides enough information to troubleshoot any deployment issues, such as a mis-spelt Project Name or folder not existing.