troubleshooting Question

PowerShell Script to Execute DTExec.exe

Avatar of D B
D BFlag for United States of America asked on
PowershellMicrosoft SQL Server 2008
6 Comments1 Solution2548 ViewsLast Modified:
I am attempting to put together a powershell script that will execute a DTS package. It will be parameter driven, in that I have a text file that contains a variable name and a value pair, and I am processing it within the script. There are three main parameters, ROOT, DtExecutable and DtCommandLine. Within the parameter file, this is what I have entered:

Key,value
ROOT,C:\Crossroads\Development\Data Conversion\SQLSCRIPTS\XX Data Conversion\Current\
DtExecutable,C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe
DtCommandLine,/FILE "%ROOT%\Extract_Master\Extract_Master.dtsx" /CONFIGFILE "%ROOT%\Extract_Master\Extract_Master.dtsConfig" /CONFIGFILE "%ROOT%\Extract_Master\EtlConnectionManager.dtsConfig" /CONNECTION "XX_EXTRACT";"\"Data Source=LOCALHOST;Initial Catalog=XX_EXTRACT;Provider=SQLNCLI10.1;Integrated Security=SSPI;\"" /SUM /CHECKPOINTING OFF /REPORTING EWD 
These are read into the variables $ROOT, $DtExecutable and $DtCommandLine in Powershell.

Within the Powershell script, I have the following lines of code:
$DtCommandLine = $DtCommandLine -replace "%ROOT%", $ROOT

write-host "Executing `"$DtExecutable`" $DtCommandLine"
& "$DtExecutable" $DtCommandLine

My output states:
...
Executing "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /FILE "C:\Crossroads\Development\Data Conversion\SQLSCRIPTS\XX Data Conversion\Current\\Extract_Master\Extract_Master.dtsx" /CONFIGFILE "C:\Crossroads\Development\Data Conversion\SQLSCRIPTS\XX Data Conversion\Curre
nt\\Extract_Master\Extract_Master.dtsConfig" /CONFIGFILE "C:\Crossroads\Development\Data Conversion\SQLSCRIPTS\XX Data Conversion\Current\\Extract_Master\EtlConnectionManager.dtsConfig" /CONNECTION "XX_EXTRACT";"\"Data Source=LOCALHOST;Initial Catalog=XX_EXTRACT;Provider=SQLNCLI10.1;Integ
rated Security=SSPI;\"" /SUM /CHECKPOINTING OFF /REPORTING EWD

Microsoft (R) SQL Server Execute Package Utility
Version 10.50.2500.0 for 32-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.

Option "Conversion\SQLSCRIPTS\XX" is not valid.

Press Enter to Exit

I am getting the error that "Conversion\SQL...\XX" is not valid. I have tried several different ways of enclosing the values in quotes, but it constantly fails. If I cut the code 'Executing "C:\Program Files ....', remove 'Executing ' and paste it into a command window everything executes fine, so Powershell is apparently doing something with the quotes, and I am too new to it to figure out what that might be.

Any suggestions?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros