SQL Server 2012 Integration Services – Using PowerShell to Configure Package Execution Parameters

In the series of introductory articles published recently on this forum, we have presented functionality incorporated into PowerShell that can be employed to create a SQL Server 2012 Integration Services catalog with an arbitrarily chosen folder structure, and deploy to it SSIS projects (representing an example of automated implementation of the new project deployment model introduced in SQL Server 2012). However, the SSIS-specific automation capabilities supported by PowerShell extend far beyond these relatively simple tasks. We will explore some of these capabilities by demonstrating the process of configuring an already deployed package in order to carry out a parameterized package execution.

In our presentation, we will assume that we have already created the Integration Services Catalog (named SSISDB) along with a single folder PowerShell Demos that will host our sample package. (For instructions regarding this setup, refer to our earlier article, SQL Server 2012 Integration Services – PowerShell-Based Project Deployment). In order to properly illustrate all of the features we are interested in, we will deploy the package we have used in the past, whose basic design is presented in the article titled SQL Server 2012 Integration Services – Package and Project Parameters. While its principles are based loosely on the pattern described in the SSIS Tutorial available from the MSDN Library, we have simplified its structure such that its sole purpose is to perform an export from the dbo.FactCurrencyRate table (residing in the AdventureWorksDW database) to a text file, rather than following the original approach, in which the content of a delimiter-separated file was imported into a database. What’s important, however, is that regardless of the direction of data transfer, we still need two connection managers (one for the SQL Server and the other for the text file) and a single Data Flow task. Format of the text file matches the structure of the table, with rows divided into four columns – AverageRate, CurrencyKey, Date, and EndOfDayRate. In addition, we have defined two package-level parameters, representing connection strings of the connection managers. To deploy this package, follow the steps outlined in our most recent article SQL Server 2012 Integration Services – PowerShell-Based Project Deployment.

With our sample package deployed, let’s review its configuration. Start by launching SQL Server Management Studio and connecting to the Database Engine hosting SSISDB Catalog. Locate the project under the Projects subfolder of the PowerShell Demos folder; you should also find there another subfolder labeled Environments, which we will focus on in our upcoming article. Right-click on the icon representing the deployed project, and select the Configure… item from its context-sensitive menu. This will display a Configure dialog box, with its Parameters tab active and the Scope. Once you set the scope to Entry-point packages and project, you should find the following three entries (as long as you were following our original instructions for configuring the package):

  • DataFlowTask_MaximumErrorCount with the scope set at the project level and the value of 1 (of the Int32 data type).
  • FlatFileConnectionManager_ConnectionString with the scope set at the package level and a value containing a full path of the output text file (of the String data type).
  • LocalAdventureWorksDW_ConnectionString with the scope set at the package level and a value containing the connection string to the local AdventureWorksDW catalog (of the String data type).

You can easily modify values of any of these parameters directly from within the Configure dialog box, however, our objective is to deliver the equivalent functionality via PowerShell. Let’s see how this can be accomplished.

We need to start by loading the Microsoft.SqlServer.Management.IntegrationServices assembly containing the relevant SSIS classes, containing required methods and properties. In addition, we also have to facilitate programmatic access to the SQL Server instance hosting the SSISDB catalog, which, in turn requires importing the SQLPS PowerShell module. These actions translate into the combination of the following two cmdlets:

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

Next, we establish connectivity to the target SQL Server instance (in our case, SERVER1SQL1) based on the connection string pointing to the master database and leveraging Windows-integrated authentication. Once this is completed, we instantiate an object representing our SSIS server and its SSISDB catalog. This object leverages the IntegrationServices class defined within the Microsoft.SqlServer.Management.IntegrationServices namespace (which became available as a result of loading the corresponding assembly):

$sqlInstance = "SERVER1SQL1"
$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"]

Now we are ready to set up PowerShell objects that correspond to the hierarchy of SSIS entities within the SSISDB catalog, visible within the SQL Server Management Studio. We start with the PowerShell Demos top level folder, which is an instance of the CatalogFolder (or more specifically, Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder) class. For this purpose, we leverage the Item property of the CatalogFolderCollection class, which we reference by utilizing the Folders property of the Catalog class (represented by the $ssisCatalog PowerShell variable). We continue descending the Integration Services Catalog component hierarchy in the equivalent manner by pointing the $ssisProject PowerShell variable to the item (named in our case Project1) of the ProjectCollection (or rather Microsoft.SqlServer.Management.IntegrationServices.ProjectCollection) class, available to us via the Projects property of the CatalogFolder class (Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder. Finally, we repeat this procedure to access our package (named simply Package.dtsx), by taking advantage of the Item property of the PackageCollection (Microsoft.SqlServer.Management.IntegrationServices.PackageCollection) class, which is accessible via Packages property of the ProjectInfo class (Microsoft.SqlServer.Management.IntegrationServices.ProjectInfo). This rather convoluted description takes the form of the following PowerShell code:

$ssisFolderName = "PowerShell Demos"
$ssisFolder = $ssisCatalog.Folders.Item($ssisFolderName)

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

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

Finally, we are in a position allowing us to directly manipulate parameters of our sample package. This is done by invoking the Set method of the ParameterInfo class (Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo), which we access by referencing the item of the ParameterCollection class (Microsoft.SqlServer.Management.IntegrationServices.ParameterCollection) identified by the $ssisParameter PowerShell variable (and to which we are assigning a literal value stored in the $ssisParameterValue PowerShell variable). Afterwards, we trigger the Alter method in order for the assignment to take effect. In the last step of our script we call the Execute method, which, in this format (the method is overloaded) accepts two parameters:

  • use32RuntimeOn64Type of the type Boolean – where True triggers 32-bit runtime, while False forces its 64-bit version.
  • reference – points to an Environment to be used during package execution. We will discuss environment configuration in our upcoming article, so for the time being, we set it to $null.

This yields the following PowerShell code, resulting in the execution of our sample package with an arbitrarily assigned parameter.

$ssisParameter = "FlatFileConnectionManager_ConnectionString"
$ssisParameterValue = "D:DataSSISProject1FactCurrencyRateOut.txt"
$ssisPackage.Parameters[$ssisParameter].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$ssisParameterValue);
$ssisPackage.Alter()

$ssisPackage.Execute("true",$null)

This concludes our presentation illustrating the use of PowerShell to configure parameters of SSIS packages deployed to Integration Services catalog.

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.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles