SQL Server 2012 Integration Services introduces an innovative approach to deploying SSIS projects, known as Project Deployment Model. This is the currently recommended (and the default) deployment technique, due to a number of benefits it delivers (such as the ability to centralize management of package properties across all deployed projects, as well as to monitor and log package execution performance and progress). However, despite these apparent advantages, the traditional, package-based methodology remains available and supported. More importantly, in some scenarios, it might be considered more viable, since it allows for separation of SQL Server Database Engine and SQL Server Integration Services (Project Deployment Model, on the other hand, requires both to reside on the same host, due to its dependency on the SSIS catalog).
In the recent articles published on this forum, we have presented the functionality incorporated into PowerShell that can be employed to manage Project Deployment Model, including such tasks as creation of the SSIS catalog and its subfolder structure, deploying individual projects to the SSIS catalog, as well as configuration of parameters and environments facilitating package execution. Now we will turn our attention to leveraging PowerShell capabilities when dealing with legacy Package Deployment Model scenarios.
As in the previous version of SQL Server Integration Services, packages can be deployed by using the Package Installation Wizard, which is invoked by double-clicking on the .SSISDeploymentManifest file, generated by setting the project’s CreateDeploymentUtility property to True prior to its compilation. (The property is accessible via the Solution Explorer window of SQL Server Data Tools). The wizard allows you to choose between two destinations – File system and SQL Server. The first of these options copies the XML-formatted package file (with extension .dtsx) along with its .dtsConfig counterpart to an arbitrary folder, with the default set to Program Files(x86)Microsoft SQL Server110DTSPackages on x64 systems. This default is supposed to provide discoverability, making newly deployed packages appear automatically under Stored PackagesFile System subfolder of the Integration Services node in the Object Explorer window of SQL Server Management Studio. The Stored PackagesFile System subfolder contains file system-based packages that have been automatically discovered by the SQL Server Integration Services 11.0 service. By default, the service automatically enumerates packages located in Program FilesMicrosoft SQL Server110DTSPackages. (Note that this is different from the location used by the Package Installation Wizard on x64 computers, which you should keep in mind if you want to take advantage of this behavior); however, it is possible to alter this location by editing the Program FilesMicrosoft SQL Server110DTSBinnMsDtsSrvr.ini.xml file and modifying the content of its StoragePath XML element. Incidentally, the same file controls other characteristics of SQL Server Integration Services 11.0 service, such as package execution behavior in scenarios where the service fails or stops (by default, the execution is halted) or the location of target SSIS instances (defined using the ServerName XML element).
The second option (labeled SQL Server deployment in the Package Installation Wizard) relies on a SQL Server instance as the package store. Once you select it, you will be prompted for the name of the server hosting the SQL Server Database Engine, an appropriate authentication method, and a path where the package should be stored (within the predefined hierarchy starting with the SSIS Packages top level node, which corresponds to the MSDB subfolder of the Stored Packages folder accessible via the Integration Services node in SQL Server Management Studio on the target server). If you want to organize your packages into a custom folder hierarchy, you will need to pre-create it (within the Stored PackagesMSDB namespace under the Integration Services node of SQL Server Management Studio). In case your package contains additional files (such as package configurations, referred to in the wizard as dependencies), you will also be given an opportunity to designate their location.
In either case, the wizard also might allow you (as controlled by the AllowConfigurationChanges project’s property, with its default set to True) to edit any existing package configurations prior to its completion. You can also decide whether you want to validate a package following its installation (which reveals the additional page labeled Packages Validation in the wizard, allowing you to identify any issues encountered during the deployment). In addition, when using SQL Server deployment, you have an option to set a package protection level (resulting in assignment of ServerStorage value to the ProtectionLevel package property). When deploying to file system, this capability is not available, forcing you to resort NTFS permissions for securing access to your packages and sensitive information they might contain.
Once completed, the package will appear in one (or possibly two) of the following locations:
- as a .dtsx file in an arbitrary folder you specified when selecting the File system option in Package Installation Wizard (which you can view by using File Explorer).
- as a Package item under the File System subfolder of the Stored Packages folder in the Integration Services node within the Object Explorer window of SQL Server Management Studio. This also implies that you will be able to locate the corresponding .dtsx file in the location associated with the StoragePath XML element of Program FilesMicrosoft SQL Server110DTSBinnMsDtsSrvr.ini.xml file (by default, pointing to the Program FilesMicrosoft SQL Server110DTSPackages folder).
- as a Package item under MSDB subfolder of the Stored Packages folder in the Integration Services node within the Object Explorer window of SQL Server Management Studio. (The exact location will match the entry you provided when selecting the SQL Server option in Package Installation Wizard).
Let’s examine how we can execute an SSIS package in each of these three cases by leveraging PowerShell capabilities. All of the required classes (with their properties and methods) that will be used throughout our code reside in the Microsoft.SqlServer.Dts.Runtime namespace, which, in turn, is incorporated into the Microsoft.SqlServer.ManagedDTS assembly (implemented as Microsoft.SqlServer.ManagedDts.dll). In order to make its content available in your PowerShell session, you need to load the assembly, which starting with Powershell 2.0, is accomplished by running Add-Type cmdlet (rather than relying on [System.Reflection.Assembly]::LoadWithPartialName method available in PowerShell 1.0 that has been deprecated). However, you might still find the legacy method helpful in determining the FullName attribute of the assembly you intend to load (required when executing the Add-Type cmdlet). In our case, we can identify it by running [System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.ManagedDTS”).FullName, which should return Microsoft.SqlServer.ManagedDTS, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91. Effectively, we will start our script with the following:
Add-Type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
Next, we have to create a new instance of the Application class, which (as described in MSDN Library) will provide access to the Package object. This involves invoking the New-Object cmdlet and storing the resulting reference in a variable (which we will name $ssisApplication):
$ssisApplication = New-Object "Microsoft.SqlServer.Dts.Runtime.Application"
In order to execute a package stored in the file system, we will first invoke the Application.LoadPackage method, which takes on two parameters: fileName (containing a full path to the .dtsx file) and events (relevant when implementing event handling, which we are not concerned about in the context of our presentation). The resulting object represents our target package (stored in the $ssisPackage variable), which we can execute by calling its Execute method:
$ssisPackagePath = "D:ProjectsPowerShellDemosDemo1Package.dtsx" $ssisPackage = $ssisApplication.LoadPackage($ssisPackagePath,$null) $ssisPackage.Execute()
Now, let’s assume that the package path we referenced is automatically discovered by the SQL Server Integration Services 11.0 service, which means that it appears under the Stored PackagesFile System subfolder of the Integration Services node in the Object Explorer window of SQL Server Management Studio. In order to execute it via PowerShell, we can take advantage of the LoadFromDtsServer method of the Application class. The method takes three parameters: sPackagePath (designating the fully qualified path of the package within the Stored Packages namespace), sServerName (containing the name of the host where the package is stored), and Events (which, just as in the previous example, is relevant when implementing event handling and, for the sake of simplicity, will be ignored here). This will result in the following code:
Add-Type -AssemblyName "Microsoft.SqlServer.ManagedDTS, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" $ssisApplication = New-Object "Microsoft.SqlServer.Dts.Runtime.Application" $ssisServer = "SERVER10" $ssisPackagePath = "File SystemPowerShellDemosDemo1Package" $ssisPackage = $ssisApplication.LoadFromDtsServer($ssisPackagePath,$ssisServer,$null) $ssisPackage.Execute()
Incidentally, we can use the same approach when executing packages stored in MSDB (corresponding to the third option we discussed earlier). The only change we need to apply to the script above is assigning a different $ssisPackagePath, such that it matches the full path to the Package item under Stored PackagesMSDB subfolder of the Integration Services node in the Object Explorer window of SQL Server Management Studio (e.g. MSDBPowerShellDemosDemo1Package).
