SQL Server 2012 Integration Services – PowerShell-Based Project Deployment

As we have demonstrated in the recent article published on this forum, you have the option of leveraging the capabilities and flexibility of PowerShell when automating management of SQL Server 2012 Integration Services. While this functionality is not as straightforward as those accustomed to traditional PowerShell cmdlets might expect, the steps required to accomplish the most common SSIS administrative tasks follow a relatively consistent pattern, which considerably helps with learning its general principles. You should be able to identify this pattern by comparing the sample code we will present here (illustrating deployment of an SSIS package to an existing SSISDB catalog) with another, recently posted script (in particular, the one whose purpose was to create such catalog).

For the sake of simplifying this objective, let’s briefly review individual steps necessary to expose SSIS management features via PowerShell. For starters, note that the majority of required methods and properties are contained within the Microsoft.SqlServer.Management.IntegrationServices assembly (implemented as Microsoft.SqlServer.Management.IntegrationServices.dll). Effectively, you will need to start by loading this assembly in order to make it available within the current PowerShell session, which is accomplished by running the following command. (Refer to our previous article for more details regarding the process of discovering proper values of Version and PublicKeyToken properties):

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

In addition, you will require programmatic access to the SQL Server instance hosting the SSISDB catalog, which necessitates importing the SQLPS module by executing Import-Module SQLPS cmdlet with the –DisableNameChecking switch (its sole purpose is to supresses the warning message triggered by the presence of non-standard verbs incorporated in the module). This action exposes classes contained within the System.Data.SqlClient namespace of .NET Framework (documented in MSDN Library), including SqlConnection, which represents an open connection to a SQL Server database (in our case, we connect to the master database residing on a named instance, leveraging Windows-integrated authentication, as determined by the connection string, such as the one provided in the code snippet that follows):

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

This connection allows us, in turn, to instantiate an object representing our SSIS server and its SSISDB catalog (assuming, of course, that the latter has already been created, as descibed in our previous article). This object leverages the IntegrationServices class defined within the Microsoft.SqlServer.Management.IntegrationServices namespace (which became available as the result of loading the corresponding assembly).

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

Projects are organized into folders (appearing as a one-level deep structure under SSISDB node in the Object Explorer window of SQL Server Management Studio ) that are supposed to reflects their intended purpose as well as any potential codependencies or relationships between them. Each folder contains two automatically created subfolders named Projects and Environments. For the purpose of our exercise, we will assume that such a folder does not exist yet and use the following code to set it up:

$ssisFolderName = "PowerShell Demos"
$ssisFolderDescription = "Created with PowerShell"
$ssisFolder = New-Object Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder($ssisCatalog, $ssisFolderName, $ssisFolderDescription)
$ssisFolder.Create()

At this point, we are ready to deploy a project developed with SQL Server Data Tools. Such project must have been designed according to (or converted to) the Project Deployment Model introduced in SQL Server 2012 Integration Services, which implies that their build takes the form of a single deployment packet .ispac file (stored in the bin subfolder of the folder containing the project files, which in our case yields the file system path of D:ProjectsPowerShellDemoDemo1.ispac). To accomplish this, we will take advantage of ReadAllBytes method of the System.IO.File class (documented in the MSDN Library), which is part of the automatically loaded mscorlib.dll assembly (hence we do not need to invoke Import-Module or Add-Type cmdlets to make it avaialble). This method loads the content of the ispac file into a byte array, which subsequently gets copied to the newly created folder by employing the DeployProject method of the CatalogFolder class.

$ssisProjectName = "PowerShell Demo 1"
[byte[]] $ssisProjectFile = [System.IO.File]::ReadAllBytes("D:ProjectsPowerShellDemosDemo1BinProductionDemo1.ispac")
$ssisFolder.DeployProject($ssisProjectName, $ssisProjectFile)

Once you refresh the Object Explorer window in SQL Server Management Studio, you should notice that the Projects subfolder under our PowerShell Demos folder now contains the Demo1 node (with the Packages subfolder, containing individual .dtsx entries representing packages incorporated in this project). If you are executing the script interactively, you should also be presented with an output at the PowerShell prompt that resembles the following (and indicates the successful deployment):

Id            : 6
OperationType : 101
CreatedTime   : 3/9/2013 10:35:47 AM +00:00
ObjectType    : 20
ObjectId      : 5
ObjectName    : Demo1
Status        : Success
CallerName    : CONTOSOAdministrator
StartTime     : 3/9/2013 10:35:47 AM +00:00
EndTime       : 3/9/2013 10:35:49 AM +00:00
StoppedByName :
Messages      : {}
Parent        : Catalog[@Name='SSISDB']
Completed     : True
IdentityKey   : Operation[@Id='6']
Urn           : IntegrationServices[@Name='SERVER1SQL1']/Catalog[@Name='SSISDB']/Operation[@Id='6']
Properties    : {Name=Id/Type=System.Int64/Writable=True/Value=6,
                Name=OperationType/Type=System.Int16/Writable=False/Value=101,
                Name=CreatedTime/Type=System.Nullable`1[System.DateTimeOffset]/Writable=False/Value=3/9/2013 10:35:47
                AM, Name=ObjectType/Type=System.Nullable`1[System.Int16]/Writable=False/Value=20...}
Metadata      : Microsoft.SqlServer.Management.Sdk.Sfc.Metadata.SfcMetadataDiscovery

This concludes our overview of deploying SSIS projects using PowerShell. In our upcoming article, we will demonstrate how to use scripting in order to automate project configuration and execution.

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