There may be an occasion when you need to call a SSIS package remotely.

If you decide to use PowerShell there are a couple of blog posts out there saying how to do a basic call a SSIS package that’s in a file.

A good example is: http://blogs.msdn.com/b/dbrowne/archive/2010/10/11/remote-ssis-package-execution-with-powershell-2-0.aspx

If however you want to use a package that is held in MSDB or uses configuration files, things start to get a little more tricky. The problem lies with invoking the dsexec command which uses lots of nasty characters like /” that cause PowerShell to  parse incorrectly.

With the help of Poshoholic over at http://powershell.org I now have a working script that you can use on any dtexec command, (I normally just use dtexecui and copy the command line into the $package variable when I need to create a new one)

Working code is below:

$package = ‘DTEXEC –% /DTS “\”\MSDB\Example\test\”" /SERVER “\”PL-SSIS\”" /CONNECTION DBConnect;”\”Data Source=PL-SQL;Initial Catalog=example;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;\”" /CHECKPOINTING OFF /REPORTING V’
invoke-command -computername DEV-SSIS -scriptblock ( $ExecutionContext.InvokeCommand.NewScriptBlock($package) )
Advertisements