Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at element61, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

SSIS Deployment with PowerShell: Adding Environment References

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:

  1. # Variables
  2. $SSIS_server ="localhost"
  3. $ProjectFilePath = "E:\Test\SSIS2012\PowerShell_Test\PowerShell_Test\bin\Development\PowerShell_Test.ispac"
  4.  
  5. $ProjectName = "PowerShell_Test"
  6. $FolderName = "PowerShellTest"
  7. $EnvironmentName = "Test2"
  8.  
  9. # Load the IntegrationServices Assembly
  10. [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;
  11.  
  12. # Store the IntegrationServices Assembly namespace to avoid typing it every time
  13. $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
  14.  
  15. Write-Host "Connecting to server ..."
  16.  
  17. # Create a connection to the server
  18. $sqlConnectionString = "Data Source=" + $SSIS_server + ";Initial Catalog=master;Integrated Security=SSPI;"
  19. $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
  20.  
  21. # Create the Integration Services object
  22. $integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection
  23.  
  24. $catalog = $integrationServices.Catalogs["SSISDB"]
  25.  
  26. Write-Host "Creating Folder " $FolderName " ..."
  27.  
  28. # Create a new folder
  29. $folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $FolderName, "Folder description")
  30. $folder.Create()
  31.  
  32. Write-Host "Deploying " $ProjectName " project ..."
  33.  
  34. # Read the project file, and deploy it to the folder
  35. [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
  36. $folder.DeployProject($ProjectName, $projectFile)
  37.  
  38. Write-Host "Creating environment ..."
  39.  
  40. $environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")
  41. $environment.Create()            
  42.  
  43. Write-Host "Adding server variables ..."
  44.  
  45. # Adding variable to our environment
  46. # Constructor args: variable name, type, default value, sensitivity, description
  47. $environment.Variables.Add("ServerName", [System.TypeCode]::String, $SSIS_server, $false, "ServerName")
  48. $environment.Variables.Add("DatabaseName",[System.TypeCode]::String, "Test2", $false,"DatabaseName")
  49. $environment.Alter()
  50.  
  51. Write-Host "Adding environment reference to project ..."
  52.  
  53. # making project refer to this environment
  54. $project = $folder.Projects[$ProjectName]
  55. $project.References.Add($EnvironmentName, $folder.Name)
  56. $project.Alter()
  57.  
  58. 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:

  1. Write-Host "Setting environment variable on package connection string ..."
  2. $ssisPackage = $project.Packages.Item("PowerShellTest.dtsx")
  3.  
  4. $parServerName = "CM.OLE_Test.ServerName"
  5. $ssisPackage.Parameters[$parServerName].Set("Referenced","ServerName")
  6.  
  7. $parDatabaseName = "CM.OLE_Test.InitialCatalog"
  8. $ssisPackage.Parameters[$parDatabaseName].Set("Referenced","DatabaseName")
  9.  
  10. $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:

  1. # Variables
  2. $SSIS_server ="localhost"
  3. $ProjectFilePath = "E:\Test\SSIS2012\PowerShell_Test\PowerShell_Test\bin\Development\PowerShell_Test.ispac"
  4.  
  5. $ProjectName = "PowerShell_Test"
  6. $FolderName = "PowerShellTest"
  7. $EnvironmentName = "Test2"
  8.  
  9. # Load the IntegrationServices Assembly
  10. [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;
  11.  
  12. # Store the IntegrationServices Assembly namespace to avoid typing it every time
  13. $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
  14.  
  15. Write-Host "Connecting to server ..."
  16.  
  17. # Create a connection to the server
  18. $sqlConnectionString = "Data Source=" + $SSIS_server + ";Initial Catalog=master;Integrated Security=SSPI;"
  19. $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
  20.  
  21. # Create the Integration Services object
  22. $integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection
  23.  
  24. $catalog = $integrationServices.Catalogs["SSISDB"]
  25.  
  26. Write-Host "Creating Folder " $FolderName " ..."
  27.  
  28. # Create a new folder
  29. $folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $FolderName, "Folder description")
  30. $folder.Create()
  31.  
  32. Write-Host "Deploying " $ProjectName " project ..."
  33.  
  34. # Read the project file, and deploy it to the folder
  35. [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
  36. $folder.DeployProject($ProjectName, $projectFile)
  37.  
  38. Write-Host "Creating environment ..."
  39.  
  40. $environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")
  41. $environment.Create()            
  42.  
  43. Write-Host "Adding server variables ..."
  44.  
  45. # Adding variable to our environment
  46. # Constructor args: variable name, type, default value, sensitivity, description
  47. $environment.Variables.Add("ServerName", [System.TypeCode]::String, $SSIS_server, $false, "ServerName")
  48. $environment.Variables.Add("DatabaseName",[System.TypeCode]::String, "Test2", $false,"DatabaseName")
  49. $environment.Alter()
  50.  
  51. Write-Host "Adding environment reference to project ..."
  52.  
  53. # making project refer to this environment
  54. $project = $folder.Projects[$ProjectName]
  55. $project.References.Add($EnvironmentName, $folder.Name)
  56. $project.Alter()
  57.  
  58. Write-Host "Setting environment variable on package connection string ..."
  59. $ssisPackage = $project.Packages.Item("PowerShellTest.dtsx")
  60.  
  61. $parServerName = "CM.OLE_Test.ServerName"
  62. $ssisPackage.Parameters[$parServerName].Set("Referenced","ServerName")
  63.  
  64. $parDatabaseName = "CM.OLE_Test.InitialCatalog"
  65. $ssisPackage.Parameters[$parDatabaseName].Set("Referenced","DatabaseName")
  66.  
  67. $ssisPackage.Alter()
  68.  
  69. Write-Host "All done."

Comments

Leave a comment on the original post [blogs.lessthandot.com, opens in a new window]

Loading comments...