Setting dynamic connection string for dtexec.exe in Powershell

This one had me banging my head on the wall and hitting Google hard until I just took a step back and tried to get inside of Powershell’s head.

Basically I have a Powershell-based build script that (among other things) needs to execute an SSIS package that initializes a database from an Excel spreadsheet, and at the end of the build process deploys a web application to IIS using the Powershell WebAdministration module. 

But here’s the catch: the Excel data support in SSIS only works in 32-bit mode and the WebAdministration module only works in 64-bit mode.  So what I need to do is launch the 32-bit version of the dtexec.exe utility from my 64-bit Powershell script.  Seems easy enough, right?  Nope.

After struggling for a while in Powershell, I switched to the good old Windows Command Prompt and managed to get it working fairly quickly using the following syntax:

"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" 
    /File "C:\Path\To\My\Package\Import Types.dtsx"
    /Conn Connection1;"Provider=SQLNCLI10;Server=MYSERVER;Database=DB_ONE;Uid=USERNAME;Pwd=PASSWORD;"
    /Conn Connection2;"Provider=SQLNCLI10;Server=MYSERVER;Database=DB_TWO;Uid=USERNAME;Pwd=PASSWORD;"
    /Set \package.variables[User::ExcelFilePath].Value;"C:\Path\To\My\Data\Import Types.Data.xls"

If you just try to slap a “&” on the front and execute it in Powershell, it doesn’t work.  I tried many variations until I arrived at this particular translation:

& "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" 
    /File "C:\Path\To\My\Package\Import Types.dtsx"
    /Conn "Connection1;`"Provider=SQLNCLI10;Server=MYSERVER;Database=DB_ONE;Uid=USERNAME;Pwd=PASSWORD;`""
    /Conn "Connection2;`"Provider=SQLNCLI10;Server=MYSERVER;Database=DB_TWO;Uid=USERNAME;Pwd=PASSWORD;`""
    /Set "\package.variables[User::ExcelFilePath].Value;C:\Path\To\My\Data\Import Types.Data.xls"

I’ve tried to make complete sense of why that particular formatting works, but I can’t.  Here’s a couple of observations about the Powershell version:

• The argument values  need to be wrapped in quotes in their entirety.
• The quotes found in the Command Prompt version need to be escaped in Powershell in order for DTExec to receive them correctly.

But that second observation is only almost true.  The /Set argument only works if you drop the embedded quotes altogether.  When I tried retaining them from the Command Prompt version and escaping them for inclusion in the Powershell, I got an error.  Dropping the quotes altogether worked, despite the embedded space in the file name.  At this point, however, I’m not arguing!