SSIS 2012 - Using PowerShell to Remotely Execute File System Packages

Monday Jul 22nd 2013 by Marcin Policht

Marcin Policht presents two PowerShell based methods of executing SQL Server 2012 Integration Services packages remotely.

In our most recent article published on this forum, we presented a PowerShell script that allows you to execute SQL Server 2012 Integration Services packages deployed by using the traditional Package Deployment Model. The approach we have described can be applied to packages stored in any of the formats available in such a scenario - in particular:

  • a .dtsx file residing in an arbitrary folder you specified during deployment.
  • a .dtsx file residing in the Program Files\Microsoft SQL Server\110\DTS\Packages folder (or any other location assigned to the StoragePath XML element of Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml file) represented by 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.
  • a Package item under the MSDB subfolder of the Stored Packages folder in the Integration Services node within the Object Explorer window of SQL Server Management Studio (its exact location matches the entry you specify during deployment).

In our previous presentation, we made the assumption (for simplicity sake) that execution will be triggered from the same computer on which the target packages reside. However, this is frequently not the case. While a package will always execute on the system where it is being launched (even if its content is stored elsewhere), it is quite common to invoke the execution remotely. (Note though that regardless of how the invocation is handled, the local installation of Integration Services is required). In this article, we will present PowerShell based methods of accomplishing this objective. (Incidentally, both of them are generic enough to facilitate the running of any remote program, rather than being limited to SSIS packages only).

While there are several ways of delivering this functionality, we will focus on the two most common:

  • PowerShell Remoting, which takes advantage of WinRM (Windows Remote Management) service (based on the Microsoft implementation of WS-Management standard). Its operating principles rely on the SOAP (Simple Object Access Protocol) message exchange transferred over HTTP and HTTPS application-layer protocols. From a usability standpoint, the process is very straightforward and involves running the Invoke-Commandcmdlet from a local PowerShell session. This, in turn, creates a session on a target system (identified by the -ComputerName parameter) that is associated with the local one. Any locally entered code (enclosed in the -scriptblock construct or identified, in the case of a script, by the -FilePath parameter) is relayed to the remote session and executed there (with an outcome sent back and presented in the local session). You have the option of making this process asynchronous in regard to the local PowerShell session by including the -AsJob switch, which will allow you to use the same session to perform other tasks while waiting for the package execution to complete (to verify status of the remote job, use the Receive-Job cmdlet).

    Alternatively, you can use the Enter-PSSession cmdlet with the -ComputerName parameter, which will start a remote interactive session (at which point, you can launch an arbitrary program or script). Effectively, any command you enter throughout its duration will be automatically executed on the target system (as indicated by the modified PowerShell prompt containing the computer name) and results you see in the session window will reflect its outcome. (To terminate it, use Exit-PSSession cmdlet). However, note that Enter-PSSession cmdlet (by virtue of its characteristics) interacts with a single system, while the -ComputerName parameter of Invoke-Command gives you the ability to target multiple computers at the same time (this capability is referred to as fan-out remoting).

    Keep in mind that, in an Active Directory environment, you should use the actual computer name as the value of the -ComputerName parameter (rather than the corresponding IP address or an DNS alias - if one exists) for either of these two cmdlets. This is required in order for the mutual Kerberos-based authentication mechanism that WinRM relies on to work properly. One way to circumvent this requirement is to configure the TrustedHosts WinRM parameter. This can be accomplished by running the Set-Item WSMan:\localhost\Client\TrustedHosts cmdlet with the -Value switch accompanied by a comma-separated list of entries (which might include IP addresses or wildcards) representing target computers that you consider trusted (you need to trust them since you are presenting them with credentials in which security context remote a PowerShell session will run). Another possible workaround relies on the certificate-based authentication, which in turn requires setting up the HTTPS listener.

    PowerShell Remoting is dependent on PowerShell 2.0 (or later), which (along with WinRM 2.0) is included in the Windows Management Framework that has to be present on both local and remote computers. The Framework components have been incorporated into the operating system since the introduction of Windows 7 and Windows Server 2008 R2. For legacy environments (starting with Windows XP and Windows Server 2003), you can download the relevant prerequisites from the Microsoft Support site.

    In order to use PowerShell Remoting, you need to enable it first (this does not apply to domain-joined Windows Server 2012 systems, where this is the default state). On an individual system, this is easily done by running Enable-PSRemoting cmdlet (you can include -Force switch to supress confirmation prompts), which will trigger the following actions (leveraging the Set-WSManQuickConfig cmdlet behind the scenes):

    • start (or restart, if already started) the WinRM service.
    • set the WinRM service to Automatic startup type.
    • create an HTTP listener with the default port 5985 to accept requests on any of the IP addresses assigned to the local system and configure PowerShell as an WinRM endpoint. Note that if you intend to use HTTPS listener (with the default port 5986), that needs to be configured separately (which is something you should consider if you are remoting to computers that do not belong to a trusted domain).
    • enable Windows Firewall exceptions for WS-Management traffic on the default HTTP listener port.

    This procedure is typically sufficient to satisfy PowerShell Remoting requirements in an Active Directory domain environment (unless your intention is to implement HTTPS listener). If you need to enforce these settings across multiple domain member computers, then you should consider leveraging Group Policy for this purpose.

    Once you enabled PowerShell Remoting on the local and remote computers (which we will refer to here as SERVER1 and SERVER2, respectively), you could execute remotely an SSIS package by invoking the DTExec.exe utility with the File switch, whose value points to the location of the Package.dtsx file (for the overview of DTExec syntax, refer to MSDN Library). For example, the following code would invoke remote execution of an SSIS package stored as D:\Data\SSIS\Lab1\Package.dtsx (Get-Credentials cmdlet triggers a dialog box prompting you for a username and password that will be used when creating the PowerShell session on SERVER2):

    $creds = Get-Credentials
    Invoke-Command -ComputerName SERVER2 -scriptblock {DTExec.exe /File "D:\Data\SSIS\Lab1\Package.dtsx"} 
    		-credential $creds
  • Invoke-WmiMethod cmdlet, which allows you to directly benefit from a wide range of features exposed by Windows Management Instrumentation (WMI). We can implement our specific objective by calling the Create method of the Win32_Process WMI class. However, unlike PowerShell Remoting, this approach relies on Remote Procedure Calls and Distributed Component Object Model connectivity between the local and remote computers, which while typically not restricted on internal corporate networks, is rarely available between subnets separated by firewalls. On the other hand, in this case, the availability of PowerShell is required only on the source computer and there is no dependency on WinRM at either source or destination (the only requirement is a healthy WMI subsystem), which means you can disregard all of the WinRM related configuration steps described above.

    Starting with PowerShell 2.0, you can use the following syntax to call an arbitrary method of a WMI class. (For more details regarding the format employed when calling WMI class and instance methods from within a PowerShell session, refer to TechNet Scripting Forum):

    Invoke-WmiMethod -Class Win32_Process -Name Create -ComputerName SERVER2 
    		-ArgumentList "cmd /c DTExec.exe /File D:\Data\SSIS\Lab1\Package.dtsx"

    Note that the output in this case does not provide you with execution details of the SSIS package, but rather lists prorperties of the Win32_Process object that was created, which are of little value to us (you could remediate it by taking advantage of the logging functionality built into DTExec.exe).

This concludes our introduction to the subject of using PowerShell to launch remote execution of SSIS packages. In our upcoming article, we will consider more advanced scenarios that take into account additional encryption and authentication requirements.

See all articles by Marcin Policht

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved