Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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
Avatar of prashanthd
Commented:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answers