SSIS DTEXEC Package Variable Space Character Not Accepted

8

I'm attempting to execute an SSIS package on SQL 2005 using the following:

dtexec /SQL "\MyPackageName" /SERVER mssql1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
 /SET "\Package.Variables[FileFolder].Value";"\\SomeServer\Someshare\Output Batch\"

this yields:

Option "Batch " is not valid.

The space at the end of the word Batch inside the quotes gave me a hint that perhaps it is treating the final backslash as an escape character. So I tried it this way:

dtexec /SQL "\MyPackageName" /SERVER mssql1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
 /SET "\Package.Variables[FileFolder].Value";"\\\\SomeServer\\Someshare\\Output Batch\\"

This allowed the package to run, but when this variable is used as the name of a flatfile to output data to, it now gives the following error:

Warning: 2010-07-27 14:36:38.23
   Code: 0x8007007B
   Source: Data Flow Task Flat File Destination [72]
   Description: The filename, directory name, or volume label syntax is incorrect.
End Warning
Error: 2010-07-27 14:36:38.23
   Code: 0xC020200E
   Source: Data Flow Task Flat File Destination [72]
   Description: Cannot open the datafile "\\\\SomeServer\\Someshare\\Output Batch\FlatFile.txt".
End Error

What gives?

ssis
package
execution
asked on Stack Overflow Jul 27, 2010 by ErikE • edited Sep 26, 2012 by ErikE

1 Answer

10

In the error message listing the erroring datafile, the single backslash after the word Batch gives the hint that perhaps only that backslash is affected.

Some testing proved out that for some strange reason, when the last character of a package variable needs to be a backslash, SSIS requires it to be doubled up. This applies even when using the GUI and choosing a job of type "SQL Server Integration Services Package" and clicking on the "Set values" tab: a trailing backslash has to be doubled up.

The final working command was:

dtexec /SQL "\MyPackageName" /SERVER mssql1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF
 /SET "\Package.Variables[FileFolder].Value";"\\SomeServer\Someshare\Output Batch\\"

With a final double-backslash.

answered on Stack Overflow Jul 27, 2010 by ErikE • edited Sep 26, 2012 by ErikE

User contributions licensed under CC BY-SA 3.0