SSIS 2012 Automation – Part 2: Create environment, map it to project and package level parameters and assign reference to project

This blog is in continuation of SSIS 2012 Automation – Part 1: Deploy SSIS Project to SSIS Catalog

In this part we will create Environment, map them to project and package level variables and assign environment reference to Project

Again I have added c# and PowerShell code so one can choose as per his\her comfort zone.

This process will help in configuring package at run time or configure it and alter in SSIS Catalog.

Make sure you are meeting all pre-requisites mentioned in part 1

C#:

//Connect to SQL server which has SSIS Package

Server SMO_Demo = new Server("dil\\denali");

//Connect to Integration Service Catalog and load project

IntegrationServices Is_Demo=new IntegrationServices(SMO_Demo);

Catalog ct_demo= Is_Demo.Catalogs["SSISDB"];

CatalogFolder folder = ct_demo.Folders["ExecuteTest"];

ProjectInfo projects= folder.Projects["BuildTestSSIS"];

//Below code creates environment for specific catalog

EnvironmentInfo environment = new EnvironmentInfo(folder, "Environment_From_CS_Code", "");

environment.Create();

environment.Variables.Add("Server",TypeCode.String,"dilkush\\sql2k5",false,"");

environment.Variables.Add("DB", TypeCode.String, "master", false, "");

environment.Variables.Add("Query", TypeCode.String, "select @@version as servername", false, "");

environment.Alter();

//Below code will add environment reference to project

projects.References.Add("Environment_From_CS_Code");

projects.Alter();

//Below code will create reference to environment variables for package parameters once we fire alter changes will reflect in package which will be saved

//in SQL Server we can run this modified package anytime later

PackageInfo package = projects.Packages["Package.dtsx"];

package.Parameters["Server"].Set(ParameterInfo.ParameterValueType.Referenced,"Server");

package.Parameters["DB"].Set(ParameterInfo.ParameterValueType.Referenced,"DB");

package.Parameters["Query"].Set(ParameterInfo.ParameterValueType.Referenced,"Query");

package.Alter();

//We can execute above changed package anytime using referencing this environment like below

EnvironmentReference rf = projects.References["Environment_From_CS_Code", "."];

rf.Refresh();

package.Execute(false,rf);

Powershell:

#Connect to SQL server which has SSIS Package

$sqlInstance = "dil\denali"

$sqlConnectionString = "Data Source=$sqlInstance;Initial Catalog=master;Integrated Security=SSPI"

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

#Connect to Integration Service Catalog and load project

$ssisServer = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices $sqlConnection

$ssisCatalog = $ssisServer.Catalogs["SSISDB"]

$ssisFolderName = "ExecuteTest"

$ssisFolder = $ssisCatalog.Folders.Item($ssisFolderName)

$ssisProjectName = "BuildTestSSIS"

$ssisProject = $ssisFolder.Projects.Item($ssisProjectName)

$ssisPackageName = "Package.dtsx"

#Below code creates environment for specific catalog

$environment = New-Object "Microsoft.SqlServer.Management.IntegrationServices.EnvironmentInfo"($ssisFolder, "Environment_from_powershell", "Env1 Desc.")

$environment.Create()

$environment.Variables.Add("Server", [System.TypeCode]::String, "dilkush\sql2k8r2", $false, "")

$environment.Alter()

$environment.Variables.Add("DB", [System.TypeCode]::String, "master", $false, "")

$environment.Alter()

$environment.Variables.Add("Query", [System.TypeCode]::String, "select @@servername as servername", $false, "")

$environment.Alter()

#Below code will add environment reference to project

$ssisProject.References.Add("Environment_from_powershell", $ssisFolder.Name)

$ssisProject.Alter()

#Below code will create reference to environment variables for package parameters once we fire alter changes will reflect in package which will be saved

#in SQL Server we can run this modified package anytime later

#considering they are project level parameters

$Server="Server"

$ssisProject.Parameters["Server"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced,$Server)

$DB="DB"

$ssisProject.Parameters["DB"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced,$DB)

$Query="Query"

$ssisProject.Parameters["Query"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced,$Query)

$ssisProject.Alter()

#Below code will create reference to environment variables for package parameters once we fire alter changes will reflect in package which will be saved

#in SQL Server we can run this modified package anytime later

#considering they are package level parameters

$ssisPackage = $ssisProject.Packages.Item($ssisPackageName)

$Server="Server"

$ssisPackage.Parameters["Server"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced,$Server)

$DB="DB"

$ssisPackage.Parameters["DB"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced,$DB)

$Query="Query"

$ssisPackage.Parameters["Query"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced,$Query)

$ssisPackage.Alter()

#We can execute above changed package anytime using referencing this environment like below

$environmentReference = $ssisProject.References.Item("Environment_from_powershell", $ssisFolder.Name)

$environmentReference.Refresh()

Write-Host $environmentReference.ReferenceId

$ssisPackage.Execute($false, $environmentReference)

Write-Host "Package Execution ID: " $executionId

Now you should be able to see references being assigned to project in SSIS Catalog.

Next step will be either executing manually or using code (already included in this part) or creating job to automate execution of this package.

Job part will be taken care in next part of this series.

Do reply with your suggestions and comments and if any then questions well!!!

Author : Dilkush(MSFT) SQL Developer Engineer, Microsoft

Reviewed by : Debarchan(MSFT) SQL Developer Engineer, Microsoft