SQL Server 2012 Integration Services – Introduction to PowerShell-based Management of SSIS

In our most recent article published on this forum, we have presented some of the fundamental principles of Windows PowerShell, applicable in the context of managing SQL Server 2012, including description of two modules (SQLPS and SQLASCMDLETS) that implement a wide range of Database Engine, Integration Services and Analysis Services-specific features. As we have demonstrated, these modules provide access to SQL Server Management Object (SMO) model, allowing you to explore its hierarchy as well as exposing methods and properties associated with its objects. Some of their functionality that represents the most common administrative tasks is available in the form of predefined and intuitively-named cmdlets (which you can easily enumerate by running Get-Command -Module SQL* command). However, PowerShell also offers the ability to directly reference methods and properties of .NET Framework classes, which while not as straightforward and database administrator-friendly as cmdlets (which, in essence, provide an abstraction layer to an underlying maze of .NET namespaces, classes, methods, and properties), give you considerably more power and flexibility. In this article, we will describe how this capability can be leveraged in order to manage SQL Server 2012 Integration Services. Note that our examples will be based on SQL Server 2012 installation on a Windows Server 2012 computer (which implies PowerShell v3).

In this presentation, we will introduce the Microsoft.SqlServer.Management.IntegrationServices namespace, documented in MSDN Library, which contains classes and interfaces that implement functionality geared towards administration of the Integration Services, focusing in particular on management of SSISDB catalog. (For more information regarding the concept of SSIS catalog, refer to one of our earlier articles). In our example, we will demonstrate how to leverage the Catalog class of the Microsoft.SqlServer.Management.IntegrationServices namespace in order to create the default SSISDB catalog (which is equivalent to the GUI-based action available via the context-sensitive menu of Integration Services Catalogs folder in the Object Explorer window of SQL Server Management Studio, once you connect to a Database Engine instance on a computer with the Integration Services feature installed). As you can determine based on the Catalog class description in MSDN Library (Inheritance Hierarchy section), its code is part of the Microsoft.SqlServer.Management.IntegrationServices assembly, implemented as Microsoft.SqlServer.Management.IntegrationServices.dll, which in turn, is stored in the Global Assembly Cache. In order to make this code available to your PowerShell session, you need to load its assembly, which starting with Powershell 2.0, can be done by running Add-Type cmdlet, rather than relying on [System.Reflection.Assembly]::LoadWithPartialName method available in PowerShell 1.0 (which has been deprecated). However, you might stil find the legacy method helpful in determining the FullName attribute of the assembly you intend to load (which you can use subsequently when running Add-Type cmdlet). For example, in our case, we can identify it by running:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices").FullName

which should return the following:

Microsoft.SqlServer.Management.IntegrationServices, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

The assembly contains definitions of a number of SSIS-specific classes, including Catalog, which is of particular interest to us, since we will employ its constructor method, as documented in MSDN Library) to create the SSISDB catalog. To verify this, you can use the Get-Types cmdlet (representing a .NET Framework method of System.Reflection.Assembly class, returned by the GetAssemblies() method, of the appdomain class) that allows you to enumerate classes included in all loaded assemblies (and which we filter based on the match of Integration string against values of their FullName attribute):

[appdomain]::CurrentDomain.GetAssemblies() | where {$_.FullName -match "Integration" } | ForEach-Object{$_.GetTypes()} | Sort-Object Name

In addition to loading this particular assembly, you also need to import the SQLPS module (by executing Import-Module cmdlet), which is required in order to establish connectivity to the SQL Server database engine instance that will host our SSISDB catalog. Interestingly, while importing this module does automatically load some more commonly used assemblies, they do not include Microsoft.SqlServer.Management.IntegrationServices, as you can confirm by enumerating its SQL Server-related assemblies with the following code:

Import-Module SQLPS –DisableNameChecking
[appdomain]::CurrentDomain.GetAssemblies() | Where {$_.FullName -match "SqlServer" } | Select FullName

In addition, creation of an SSIS catalog, requires that the CRL integration server option is enabled. This can be accomplished via a sequence of statements executed from a New Query window in SQL Server Management Studio while connected to the SQL Server instance that will host the SSIS catalog.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Let’s assume that our Integration Services installation resides on the server called SERVER1 that hosts a named Database Engine instance SQL1, configured with Windows Authentication mode (if you are using the default instance, simply set the $sqlInstance variable to SERVER1 or localhost). Our SSIS catalog will be called SSISDB and we will secure its content with S3cr3Tw0rd password (these values constitute two of three parameters used when invoking constructor of Microsoft.SqlServer.Management.IntegrationServices.Catalog class, as documented in MSDN Library). Effectively, the resulting PowerShell script will take the following format (note, that for the sake of simplicity, we have not implemented any error checking, which should be included in any production code):

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

$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 = New-Object Microsoft.SqlServer.Management.IntegrationServices.Catalog ($ssisServer, "SSISDB", "S3cr3Tw0rd")
$ssisCatalog.Create()

This concludes our introduction to the use of PowerShell in leveraging .NET Framework functionality for managment of SQL Server 2012 Integration Services. In our upcoming articles, we will discuss more advanced scenarios that illustrate how this methodology can be applied in automating most common SSIS administrative tasks.

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