SQL Server 2012 Integration Services – Using PowerShell to Configure Project Environments

In the recent articles published on this forum, we have been discussing how to leverage the capabilities of PowerShell to automate the most common SQL Server Integration Services administrative task, focusing in particular on the project deployment model introduced in SQL Server 2012. We started with sample scripts, illustrating the process of loading the required .NET assemblies and setting up basic components to provide an ecosystem for future SSIS projects and packages (such as SSISDB catalog or a folder structure within it). Equipped with this basic knowledge, we next followed by describing the steps necessary to deploy projects and run packages, including configuration of package execution parameters. Now it is time to explore some of the more complex management tasks by demonstrating the use of PowerShell in implementing and utilizing project environments.

As you might recall from our earlier post on this subject (refer to SQL Server 2012 Integration Services – Using Environments in Package Execution for details), SQL Server 2012 offers the ability to assign values of project and package-level parameters when invoking their execution. This is accomplished by employing either environments or configurations (which serve the equivalent role in the new project deployment model and the legacy package deployment model respectively). Conceptually, both of them constitute collections of arbitrary variables and their values. In the case of environments, once defined these variables can be subsequently mapped to parameters of projects and packages stored in SSISDB catalog. One possible (and fairly common) usage of this feature involves designating parameters representing data store connection strings during the project design stage and assigning to them values appropriate for Development, UAT, and Production stages by creating three environments, each containing a set of values representing connection strings for development, UAT, and production servers. We will illustrate implementation of such configuration, although for the sake of simplicity we will limit it to a single configuration (at that point, creating additional ones becomes a trivial task).

In our presentation, we will leverage the sample package we have used on a number of occasions (for instructions regarding its setup, refer to our earlier post). Its design is based on the example described in the SSIS Tutorial available from the MSDN Library. In our version, the package performs an export from the dbo.FactCurrencyRate table residing in the AdventureWorksDW database to a text file. This means that our package contains two connection managers (one for the SQL Server and the other for the text file) and a single Data Flow task. The format of the output text file matches the structure of the table, with rows divided into four columns – AverageRate, CurrencyKey, Date, and EndOfDayRate.

As part of our design, we have defined two package-level parameters, representing the connection strings of the two connection managers (note that this is easily done by invoking the Parameterize… option from the context-sensitive menu of each of them) and kept their default names of FlatFileConnectionManager_ConnectionString and LocalhostAdventureWorksDW_ConnectionString. We will assume that we already have created SSISDB Integration Services Catalog with a folder named PowerShellDemos, as well as that the project (named Project1) containing the sample package (named Package.dtsx) has been deployed to it (refer to our earlier article to find the PowerShell script that delivers this functionality).

Our objective is to define an environment and a single variable that will be used to populate the value of FlatFileConnectionManager_ConnectionString parameter of the sample package during its execution. In other words, we want to be able to save an output of data export (represented by the FlatFileConnectionManager_ConnectionString parameter of the Flat File Connection Manager in our package) to a designated file, whose specific name and location depends on whether the execution is part of the Production processing. In order to accomplish this, we will need to carry out the following sequence of steps:

  • create an environment named Prod (as mentioned before, we will limit the scope to a single environment only, but setting up additional ones can be done simply by duplicating the provided code).
  • add a variable (storing a value of FlatFileConnectionManager_ConnectionString intended for the package execution in the Prod implementation) to the newly created environment.
  • link the Prod environment and the project by creating a reference in the latter.
  • set the package parameter to the value of the environment variable.
  • execute the package while referencing the Prod environment.

As usual, we will start by loading the Integration Services .NET assembly and importing the SQLPS module. We will also establish a Windows integrated authentication connection to the target SQL Server (named SERVER1) and navigate to the sample package (Package.dtsx) of our project (Project1) deployed to the PowerShellDemos folder within the SSISDB catalog:

Add-Type -AssemblyName "Microsoft.SqlServer.Management.IntegrationServices, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Import-Module SQLPS –DisableNameChecking

$sqlInstance = "SERVER1"
$sqlConnectionString = "Data Source=$sqlInstance;Initial Catalog=master;Integrated Security=SSPI"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

$ssisServer = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices $sqlConnection
$ssisCatalog = $ssisServer.Catalogs["SSISDB"]

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

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

$ssisPackageName = "Package.dtsx"
$ssisPackage = $ssisProject.Packages.Item($ssisPackageName)

Now we can proceed with the first of the steps listed above by creating an environment (which in programming terms translates into an instance of EnvironmentInfo class within the Microsoft.SqlServer.Management.IntegrationServices namespace, whose characteristics are described in MSDN Library). Since environments are scoped on the folder level, we need to provide the reference to the PowerShellDemos folder).

$ssisEnvironmentProd = New-Object "Microsoft.SqlServer.Management.IntegrationServices.EnvironmentInfo" ($ssisFolder, "Prod", "Prod Environment")

With the environment in place, we are ready to add a new variable to it. EnvironmentInfo. The Variables property (as per MSDN Library), represents a collection of environment variables. You can populate it by employing the Add method, which according to its documentation takes five parameters:

  • varName – name of the variable
  • varType – type of the variable
  • varValue – value of the variable
  • varSensitive – case sensitivity of the variable (Boolean true data type)
  • varDescription – description of the variable

Since the variable is supposed to store the connection string of a text file, it is of a String data type and contains its full file system path. We store the name of the environment variable in the PowerShell variable named $ssisEnvironmentProd_FFCM_CS, since we will need to reference it later in the same script. We have to call the Alter method of the EnvironmentInfo class afterwards in order for the change to take effect:

$ssisEnvironmentProd_FFCM_CS = "FlatFileConnectionManager_ConnectionString"
$ssisEnvironmentProd.Variables.Add($ssisEnvironmentProd_FFCM_CS, [System.TypeCode]::String , "D:DataProdProject1FactCurrencyRateProd.txt", $False, "Flat File Connection Manager Connection String")	

In order for our projects to reference the intended environment, we use one of two variants of the overloaded EnvironmentReferenceCollection.Add method, which takes two string parameters, representing respectively the name of the environment and its folder (as documented in MSDN Library). Just as before, the Alter method (of the Project class in this case) is required to make the change persist:

$ssisProject.References.Add($ssisEnvironmentProd.Name, $ssisFolder.Name)

Now we need to assign the value of the environment variable to the appropriate package parameter by calling the Set method of the ParameterInfo class. We are relying on the referenced (rather than literal) value, stored in the $ssisEnvironmentProd_FFCM_CS PowerShell variable we created earlier. Once again, we take advantage of the Alter method (although this time applicable to the PackageInfo class) to make our change effective:

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

Finally, we retrieve a reference to the Prod environment (implemented as an instance of the project’s references collection) and call its Refresh method to ensure it is current. At this point, we can finally launch the package, including the environment reference as one of the parameters of the Execute method:

$ssisEnvironmentReference = $ssisProject.References.Item($ssisEnvironmentProd.Name, $ssisFolder.Name)
$ssisPackage.Execute("False", $ssisEnvironmentReference)

This concludes our sample script, which demonstrates the use of PowerShell to confgure project environments and perform parameterized package executions.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles