Avatar of D B
D B
Flag for United States of America asked on

PowerShell Script to Execute DTExec.exe

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 

Open in new window

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

Open in new window


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

Open in new window


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?
PowershellMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
D B

8/22/2022 - Mon
oleggold

try escape characters with the quotes ,e.g.\'
D B

ASKER
oleggold: I already tried that (I placed the entire string in the parameter file in quotes and escaped all of the internal quotation marks. That also failed.)
D B

ASKER
As stated in the original post, if I copy lines 2-4 from the output of the last code block, paste it into a command window and remove 'Executing ' from the start of the string, the code executes. It seems like the the write-host line is displaying it correctly, but the & "$DtExecutable" $DtCommandLine isn't.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
prashanthd

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
D B

ASKER
prashanthd: I'll give it a try, although not the preferred way of resolving this. I am getting very frustrated with this.

I've used EchoArgs.exe (http://edgylogic.com/blog/powershell-and-external-commands-done-right/) and it is parsing $dtCommandLine as such:

Arg 0 is </FILE C:Project\Development\Data>
Arg 1 is <Conversion\SQLSCRIPTS\DB_DataConversion\Current\DB_Extract_Master\DB_Extract_Master.dtsx /CONFIGFILE C:\Project\Development\Data>
Arg 2 is <Conversion\SQLSCRIPTS\DB_DataConversion\Current\DB_Extract_Master\DB_Extract_Master.dtsConfig /CONFIGFILE C:\Project\Development\Data>
Arg 3 is <Conversion\SQLSCRIPTS\DB_DataConversion\Current\DB_Extract_Master\EtlConnectionManager.dtsConfig /CONNECTION DB_EXTRACT;"Data>
Arg 4 is <Source=LOCALHOST;Initial>
Arg 5 is <Catalog=DB_EXTRACT;Provider=SQLNCLI10.1;Integrated>
Arg 6 is <Security=SSPI;" /SUM /CHECKPOINTING OFF /REPORTING EWD >

What I'd really like to know is how to format the string either when I read it into $stCommandLine at the start, or within the csv file in which the value is stored so that PS will see it as a single string.
D B

ASKER
Much as I hate doing it this way, between EE and an MSDN forum, this is the only solution that works.
Thanks