With the release of the revamped Integration Services in SQL Server 2012, a bunch of new deployment methods were introduced for the project deployment model. My article SSIS Deployments with SQL Server 2012 gives an overview of these deployment methods. One of these methods is using PowerShell to deploy your project to the SSIS Catalog. Matt Masson (blog | twitter) has an excellent blog post on the subject: Publish to SSIS Catalog using PowerShell.
However, there’s one small step missing in the deployment script posted by Matt. I’ll use this blog article to post the entire deployment script, so that I have a full script online as an easy reference.
I have a very simple SSIS project that I want to deploy to the Catalog. It contains only one package that transfers data from AdventureWorks to another database. It has two connection managers: one for each database. Nothing fancy here.
When I deploy the project, I want to hook the OLE_TEST connection manager to an environment, so that I can easily change the destination server and/or database.
The original script from Matt’s blog – modified for my project – looks like this:
- # Variables
- $SSIS_server ="localhost"
- $ProjectFilePath = "E:\Test\SSIS2012\PowerShell_Test\PowerShell_Test\bin\Development\PowerShell_Test.ispac"
- $ProjectName = "PowerShell_Test"
- $FolderName = "PowerShellTest"
- $EnvironmentName = "Test2"
- # Load the IntegrationServices Assembly
- [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;
- # Store the IntegrationServices Assembly namespace to avoid typing it every time
- $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
- Write-Host "Connecting to server ..."
- # Create a connection to the server
- $sqlConnectionString = "Data Source=" + $SSIS_server + ";Initial Catalog=master;Integrated Security=SSPI;"
- $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
- # Create the Integration Services object
- $integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection
- $catalog = $integrationServices.Catalogs["SSISDB"]
- Write-Host "Creating Folder " $FolderName " ..."
- # Create a new folder
- $folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $FolderName, "Folder description")
- $folder.Create()
- Write-Host "Deploying " $ProjectName " project ..."
- # Read the project file, and deploy it to the folder
- [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
- $folder.DeployProject($ProjectName, $projectFile)
- Write-Host "Creating environment ..."
- $environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")
- $environment.Create()
- Write-Host "Adding server variables ..."
- # Adding variable to our environment
- # Constructor args: variable name, type, default value, sensitivity, description
- $environment.Variables.Add("ServerName", [System.TypeCode]::String, $SSIS_server, $false, "ServerName")
- $environment.Variables.Add("DatabaseName",[System.TypeCode]::String, "Test2", $false,"DatabaseName")
- $environment.Alter()
- Write-Host "Adding environment reference to project ..."
- # making project refer to this environment
- $project = $folder.Projects[$ProjectName]
- $project.References.Add($EnvironmentName, $folder.Name)
- $project.Alter()
- Write-Host "All done."
The script deploys the project to the Catalog, creates an environment and links the environment to the project. The environment has two variables: one to set the server name and one to set the database. Let’s inspect the results. When you right-click on the project, you can choose Configure. In the Configure window, you can see the connection managers used in the project in the Connection Managers tab. When we take a look at the Initial Catalog property of the OLE_TEST connection manager, you can see it is not yet linked to an environment variable.
Instead, it is still linked to the default design-time value. Remark that you can configure a package/project with an environment without actually using parameters. This is because a few properties of a connection manager are linked to parameters behind the scenes. For example, the Initial Catalog property is linked to the parameter with the name [CM.<connection manager name>.InitialCatalog]. You can find the parameter name at the top of the Set Parameter Value dialog box.
To link the environment variables to the connection manager, we need to add just a few lines to the script:
- Write-Host "Setting environment variable on package connection string ..."
- $ssisPackage = $project.Packages.Item("PowerShellTest.dtsx")
- $parServerName = "CM.OLE_Test.ServerName"
- $ssisPackage.Parameters[$parServerName].Set("Referenced","ServerName")
- $parDatabaseName = "CM.OLE_Test.InitialCatalog"
- $ssisPackage.Parameters[$parDatabaseName].Set("Referenced","DatabaseName")
- $ssisPackage.Alter()
When you deploy the project to the server with these adjustments, we get the result we want:
The environment variables are now linked to the two properties of the connection manager. When you run the package and choose the environment, the data is transferred to another database different from the one configured in the package, due to the reconfiguring of the connection manager by the environment variables.
To wrap-up this post, here’s the entire deployment script for easier copy-paste:
- # Variables
- $SSIS_server ="localhost"
- $ProjectFilePath = "E:\Test\SSIS2012\PowerShell_Test\PowerShell_Test\bin\Development\PowerShell_Test.ispac"
- $ProjectName = "PowerShell_Test"
- $FolderName = "PowerShellTest"
- $EnvironmentName = "Test2"
- # Load the IntegrationServices Assembly
- [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;
- # Store the IntegrationServices Assembly namespace to avoid typing it every time
- $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
- Write-Host "Connecting to server ..."
- # Create a connection to the server
- $sqlConnectionString = "Data Source=" + $SSIS_server + ";Initial Catalog=master;Integrated Security=SSPI;"
- $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
- # Create the Integration Services object
- $integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection
- $catalog = $integrationServices.Catalogs["SSISDB"]
- Write-Host "Creating Folder " $FolderName " ..."
- # Create a new folder
- $folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $FolderName, "Folder description")
- $folder.Create()
- Write-Host "Deploying " $ProjectName " project ..."
- # Read the project file, and deploy it to the folder
- [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
- $folder.DeployProject($ProjectName, $projectFile)
- Write-Host "Creating environment ..."
- $environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")
- $environment.Create()
- Write-Host "Adding server variables ..."
- # Adding variable to our environment
- # Constructor args: variable name, type, default value, sensitivity, description
- $environment.Variables.Add("ServerName", [System.TypeCode]::String, $SSIS_server, $false, "ServerName")
- $environment.Variables.Add("DatabaseName",[System.TypeCode]::String, "Test2", $false,"DatabaseName")
- $environment.Alter()
- Write-Host "Adding environment reference to project ..."
- # making project refer to this environment
- $project = $folder.Projects[$ProjectName]
- $project.References.Add($EnvironmentName, $folder.Name)
- $project.Alter()
- Write-Host "Setting environment variable on package connection string ..."
- $ssisPackage = $project.Packages.Item("PowerShellTest.dtsx")
- $parServerName = "CM.OLE_Test.ServerName"
- $ssisPackage.Parameters[$parServerName].Set("Referenced","ServerName")
- $parDatabaseName = "CM.OLE_Test.InitialCatalog"
- $ssisPackage.Parameters[$parDatabaseName].Set("Referenced","DatabaseName")
- $ssisPackage.Alter()
- Write-Host "All done."
