Running SSIS package Remotely using PowerShell
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.
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) )