다음을 통해 공유

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

Comments

  • Anonymous
    November 19, 2013
    How do you drop or delete only a environment reference using powershell?

    • Anonymous
      January 18, 2017
      Write-Host "Checking " $environment " environment ..."if($FolderName.Environments.Contains($EnvironmentName)){ Write-Host "Droping " $environment " environment ..." $FolderName.Environments.Items($EnvironmentName).Drop()}Write-Host "Creating " $environment " environment ..."$environment = New-Object $ISNamespace".EnvironmentInfo" ($folderName, $EnvironmentName, "Description")$environment.Create()

  • Anonymous
    November 13, 2014
    Some very useful info about, particularly that you have to add the environment reference to the project and do Project.Alter. I do not get a valid EnvironmentReference object using this method.

  • Anonymous
    November 14, 2014
    The comment has been removed

    • Anonymous
      April 18, 2016
      It's because you are committing changes to the server, and need to get them back again. The whole model seems to make use of the calls to .Alter() to "commit" your changes.Thank you Snehadeep, this was incredibly useful. Much appreciated.

  • Anonymous
    September 30, 2016
    how do I drop environment if already exist

  • Anonymous
    March 13, 2017
    Hello I got exception on these lines can you help me$ssisProject.References.Add("Environment_from_powershell", $ssisFolder.Name)$ssisProject.Alter()Exception calling "Add" with "2" argument(s): "An entry with the same key already exists."At D:\SSIS\environment.ps1:112 char:13+ $ssisProject.References.Add("Environment_from_powershell" ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : ArgumentException