0

In a "child.cmd" file, I have the following command:

DTEXEC.EXE /FILE "\"%~dp0..\SSIS Packages\%stepName%.dtsx\"" /CONNECTION DestinationConnectionOLEDB;"\"Data Source=%serverName%;Initial Catalog=%databaseName%;Provider=%databaseProvider%;%security%;Auto Translate=false;\"" /CONNECTION SourceConnectionExcel;"\"Provider=%excelProvider%;Data Source=%~dp0..\Excel Import Files\BAs Code Tables.xlsx;Extended Properties=%extendedProperties%;HDR=YES;\"" /CHECKPOINTING OFF  /REPORTING %reporting% > "%~dp0..\Logs\Import\%stepName%.txt"

Essentially, I'm using DTEXEC.EXE to execute an SSIS package, and send the output to a text file. If I double click "child.cmd" everything works great.

However, if I try and call "child.cmd" from another batch file, say "parent.cmd", it no longer works. My "parent.cmd" file looks like:

call "%~dp0""Batch Files\child.cmd"

When I run "parent.cmd", it seems that the parameters I'm trying to pass into DTEXEC.EXE in my "child.cmd" file are no longer formatted the same, which causes the error:

Option "Files\..\SSIS" is not valid.

Where "Files..\SSIS" is a part of the /FILE file path input. There is a space before the "Files" portion of that path. Does anyone know what is going on here?

Note: A lot of the variables return text in double quotation marks because there are spaces. I feel like anywhere there are spaces is what is causing the problem. I don't know why the problem is only showing up when trying the run it as a child batch.

Edit: "child.cmd" is a folder deeper than "parent.cmd". I have updated my code above where I'm calling "child.cmd" in "parent.cmd". In addition, I do not set CD in my cmd files, and my googling seems to show that is not particularly easy to do when you don't know what file location "parent.cmd" is going to be run from, as is the case here.

Andrew
  • 11
  • 4
  • `/FILE "%~dp0..\SSIS Packages\%stepName%.dtsx"` (remove `\"` from `/FILE` option string). I only guess that `\"` in both `/CONNECTION` option strings could be O.K. – JosefZ Nov 12 '15 at 23:01
  • Thanks for the response @JosefZ. I have tried that, with no luck. The error is slightly different, complaining: Option "Implementation\P3" is not valid. Which is a similar type of error in a different spot of the /FILE input. The weird thing is that if I paste the full path in double quotes (not using %~dp0), it works. However, I need the ability to use %~pd0, so that's more interesting in that it seems to be a problem with how %~pd0 is combined with the rest of the path... – Andrew Nov 12 '15 at 23:37
  • The path that %~dp0 would be returning is: ... Implementation\P3 - Design\Data Conversion\Deliver to Client\Batch Files\ – Andrew Nov 12 '15 at 23:40
  • 1
    Please [edit] _your_ question rather than comment it as StackExchange sites are not chat-based. Read [Welcome to Stack Overflow](http://stackoverflow.com/tour) tour; doing that costs no more than two minutes... – JosefZ Nov 13 '15 at 07:40

1 Answers1

0

I suggest for file parent.cmd:

@echo off
rem Define variable CmdPath with path of directory containing this command
rem file ending with directory separator backslash and used also by the
rem called child command file.

set "CmdPath=%~dp0"
call "%CmdPath%Batch Files\child.cmd"
set "CmdPath="

I suggest for file child.cmd after reading the Microsoft TechNet article about dtexec Utility (SSIS Tool):

@echo off
rem Variable CmdPath is defined usually in parent command file. But if just
rem this command file is executed and therefore the variable CmdPath is not
rem defined, variable CmdPath is defined here in this file with path of the
rem parent directory of the directory containing this command file.

if "%CmdPath%" == "" (
    rem Command FOR is used to get path of parent directory already
    rem expanded with directory separator backslash at end appended.
    for /F "delims=" %%D in ("%~dp0..\") do set "CmdPath=%%~fD"
)
dtexec.exe /File "%CmdPath%SSIS Packages\%stepName%.dtsx" /Connection "DestinationConnectionOLEDB;Data Source=%serverName%;Initial Catalog=%databaseName%;Provider=%databaseProvider%;%security%;Auto Translate=false" /Connection "SourceConnectionExcel;Provider=%excelProvider%;Data Source=%CmdPath%Excel Import Files\BAs Code Tables.xlsx;Extended Properties=%extendedProperties%;HDR=YES" /CheckPointing off  /Reporting "%reporting%" >"%CmdPath%Logs\Import\%stepName%.txt"

The comments explain the method used here to get path of directory containing parent.cmd by whichever command file is executed first.

For understanding the used commands and how they work, open a command prompt window, execute there the following commands, and read entirely all help pages displayed for each command very carefully.

  • call /?
  • for /?
  • if /?
  • rem /?
  • set /?
Mofi
  • 46,139
  • 17
  • 80
  • 143