Execute SQL Server Integration Services (SSIS) Package from Powershell

***Updated 2/23/2009. After some additional testing I discovered you cannot change the location of SSISConfig without saving and reloading a package or exporting and importing a configuration, so I removed the functionality to reset the config location since there isn’t an easy way of doing so. In addition errors were not being automatically enumerated. The script has been updated to return errors. One open question I have is whether executing SSIS packages using the ManagedDTS class executes under x64 or x86. I will post an update once I determine this.

Based on the work I’ve done with Powershell and SSIS in SQL Server Powershell Extensions, I created a standalone Powershell script to execute SSIS packages.   The script is available on Poshcode here.  The Powershell script offers several advantages over the dtexec:

  1. Powershell is a .NET language so you can access SSIS programming model
  2. Logging to the Windows Application Event log is optional
  3. Full error messages are returned
  4. Return codes are set by the throw statement
  5. Execute both server and file based SSIS packages
  6. Verifies existence of package before execution

Note:  SSIS is NOT backwards compatible. At the beginning of the script you’ll need to comment/uncomment the specific assembly to load 2005 or 2008. Currently the script is set to 2005 

Script Examples/Description:

 Executes SSIS package for both server and file system storage types.

 ————————– EXAMPLE 1 ————————–

 ./RunSSIS.ps1 -path Z002_SQL1sqlpsx -serverName ‘Z002SQL1’

 This command will execute package sqlpsx on the server Z002SQL1

 ————————– EXAMPLE 2 ————————–

 ./RunSSIS.ps1 -path Z002_SQL1sqlpsx -serverName Z002SQL1 -configFile ‘C:SSISConfigsqlpsx.xml’

 This command will execute the package as in Example 1 and process and configuration file

  ————————– EXAMPLE 3 ————————–

 ./RunSSIS.ps1 -path ‘C:SSISsqlpsx.dtsx’

 This command will execute the package sqlpsx.dtsx located on the file system

  ————————– EXAMPLE 4 ————————–

 ./RunSSIS.ps1 -path ‘C:SSISsqlpsx.dtsx -nolog

 This command will execute the package sqlpsx.dtsx located on the file system and skip Windows Event logging

Advertisements
This entry was posted in PowerShell. Bookmark the permalink.

9 Responses to Execute SQL Server Integration Services (SSIS) Package from Powershell

  1. Greg says:

    I’m trying to use your latest script and when I run it using the file system and config option I get a Microsoft Visual C++ Runtime library error that states: Program:C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exeR6034 An Application has mad an attempt to load the Cruntime Library incorrectly.

  2. Chad says:

    This is PowerShell Library, which is just a bunch of PowerShell functions intepreted at runtime from within the PowerShell host. It’s not a C++ Library.

  3. Danie says:

    I’m trying to use your script for executing my SSIS packages, but I do not use a configuration file but a table in the database for the configuration.
    Can you tell what I have to change for using the configuration table?

    Thanks.

    • cmille19 says:

      I believe table configurations and for that matter even file configuration are defined within the package and the script’s option to specify a configuration file is unnecessary unless you want to do so at runtime.

      Keep in mind I’m just using the Package Class which is part of the DTS.Runtime namespace. You can read about the properties and methods available on the Package here:
      http://msdn.microsoft.com/es-es/library/ms213040.aspx

  4. prateep says:

    Can we compress the files is SSIS package by using Poweshell

  5. Laura says:

    When running RunSSIS.ps1, if I specify a DNS alias name in the -servername parameter, it fails because it can’t load the package. However, if I use the physical server name where the package is stored, it works fine. I can connect to Integration Services using the DNS alias in Management Studio. Any idea why it won’t work with this powershell script?

    • cmille19 says:

      I’m not sure I haven’t that issue. The script supposedly uses the same assembly as SSMS uses.

      • Laura says:

        As I do more testing it seems that it works from other servers when using the alias but it does not work from the server that is the server aliased. I also tried to connect via SSMS using the DNS Alias and it does NOT work on the server aliased. It only works from a different server or pc. So, as you said, it is behaving the same as SSMS and it is not specific to powershell. Thanks for your quick response and I will continue to look into it.

Comments are closed.