syntax for passing mutliple variable using dtexec command

Question

Post reply

syntax for passing mutliple variable using dtexec command

OceanDeep

Ten Centuries

Points: 1078
More actions
May 19, 2009 at 1:41 pm

#221392

I am able to pass one variable sucessfully when executing SSIS package in command line but not sure what syntax is for multiple variables. Below is the command I have for one variable.
dtexec /F "C:\DTSPackage\MySolution.dtsx" /SET "\Package.Variables[employeeID].Value";9
I have two more variables to pass and they are EmployeeType and OfficeID which are string and numeric respectively. They both are in the same path as employeeID variable. Please advise the syntax.
OD

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

Try this:

dtexec /F "C:\DTSPackage\MySolution.dtsx" /SET "\Package.Variables[employeeID].Value";9 /SET "\Package.Variables[OtherVar].Value";9

Greets

Flo

OceanDeep Ten Centuries Points: 1078 More actions · Answer 2

Thanks for the reply. Actually I did try that before I posted my question, it returned an syntax error. Your command is almost exactly like mine except that my second variable is a string. Your answer got me thinking that I forgot to put the " to enclose the value string. Once I did that, it works fine.

By the way, I am working with a flat file connectin property and try to change the row delimiter. Do you have any experience changing the row delimiter from '' to some custom string like '&LC&'. I defined a variable for the custom row delimiter and then use the expressioin editor to assign it to HeadRowDelimiter and Row Delimiter. After I saved the changes, these two field's property are correctly filled with &CL&. But when I use 'Edit' to verify the value in the 'Advanced' properties of the last column in that flat file connection, it still shows . Do you know what I did wrong or you have a better way to do it?

od

COZYROC One Orange Chip Points: 28499 More actions · Answer 3

OceanDeep (5/19/2009)
Thanks for the reply. Actually I did try that before I posted my question, it returned an syntax error. Your command is almost exactly like mine except that my second variable is a string. Your answer got me thinking that I forgot to put the " to enclose the value string. Once I did that, it works fine.
By the way, I am working with a flat file connectin property and try to change the row delimiter. Do you have any experience changing the row delimiter from '' to some custom string like '&LC&'. I defined a variable for the custom row delimiter and then use the expressioin editor to assign it to HeadRowDelimiter and Row Delimiter. After I saved the changes, these two field's property are correctly filled with &CL&. But when I use 'Edit' to verify the value in the 'Advanced' properties of the last column in that flat file connection, it still shows . Do you know what I did wrong or you have a better way to do it?
od

This is not so easy as it seems. You have to modify the last column delimiter and I'm not sure if this will possible using configuration file (you have to use script task). Check this message thread for more information.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

OceanDeep Ten Centuries Points: 1078 More actions · Answer 4

thank for the reply. The thread that you pointed me to actually help me to solve it although it is not pretty. The thread mentioned about opening the package using text editor, which I didn't know I could do. Since I can open the package with text editor, I use the find-and-replace feature to replace all the which is _x000D__x000A_ with my custom row delimiter. I ran the package and wa..la.... all text files has the new custom row delimiter. Now I am trying to figure out if i can automate this step. Any thought?

OD

COZYROC One Orange Chip Points: 28499 More actions · Answer 5

OceanDeep (5/20/2009)
thank for the reply. The thread that you pointed me to actually help me to solve it although it is not pretty. The thread mentioned about opening the package using text editor, which I didn't know I could do. Since I can open the package with text editor, I use the find-and-replace feature to replace all the which is _x000D__x000A_ with my custom row delimiter. I ran the package and wa..la.... all text files has the new custom row delimiter. Now I am trying to figure out if i can automate this step. Any thought?
OD

Well,

One fast solution would be:

1. Create a script task.

2. Open and read the SSIS package into string.

3. Search for the special combination (which works for you) and replace with the new delimiter.

Check here[/url] for sample.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

OceanDeep Ten Centuries Points: 1078 More actions · Answer 6

ok, I will try that. Can you tell me what wrong with the following command?

dtexec /F "C:\DTSPackage\MySolution.dtsx" /SET "\Package.Variables[EmployeeID].Value";162 /SET "\Package.Variables[OfficeCode].Value";"10" /SET "\Package.Variables[sFilePath].Value";"N:\exportfile\"

When I ran this in the command prompt, it gives me this error:

The agrument "\Package.Variables[sFilePath].Value";"N:\exportfile\" " has mismatched quotes.

The first two arguments ran without problem. When I added this third one, I encounter this error. I looked at the syntax and can't find anything wrong since it is contructed just like the other two. I am so puzzled. This third agrument is for setting the file path where the exported files will reside.

OD

COZYROC One Orange Chip Points: 28499 More actions · Answer 7

Try this:

dtexec /F "C:\DTSPackage\MySolution.dtsx" /SET "\Package.Variables[EmployeeID].Value;162" /SET "\Package.Variables[OfficeCode].Value;10" /SET "\Package.Variables[sFilePath].Value;N:\exportfile"

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

OceanDeep Ten Centuries Points: 1078 More actions · Answer 8

Your command got the same error....... but guess what..... your new command again showed me something different... For the last argument, you remove the last '\' from the file path, N:\exportfile. So I used my same command that I had before and removed the last '\' as follows:

dtexec /F "C:\DTSPackage\MySolution.dtsx" /SET "\Package.Variables[EmployeeID].Value";162 /SET "\Package.Variables[OfficeCode].Value";"10" /SET "\Package.Variables[sFilePath].Value";"N:\exportfile"

Well, it ran successfully but because there is no \ at the end, all my export files' file name become exportfilexxxxxxxx.txt. Boy, this thing is quite buggie..... Any thought on how to address this.

I like to thank you for hanging with me on this.

od

COZYROC One Orange Chip Points: 28499 More actions · Answer 9

OceanDeep (5/20/2009)
Your command got the same error....... but guess what..... your new command again showed me something different... For the last argument, you remove the last '\' from the file path, N:\exportfile. So I used my same command that I had before and removed the last '\' as follows:
dtexec /F "C:\DTSPackage\MySolution.dtsx" /SET "\Package.Variables[EmployeeID].Value";162 /SET "\Package.Variables[OfficeCode].Value";"10" /SET "\Package.Variables[sFilePath].Value";"N:\exportfile"
Well, it ran successfully but because there is no \ at the end, all my export files' file name become exportfilexxxxxxxx.txt. Boy, this thing is quite buggie..... Any thought on how to address this.
I like to thank you for hanging with me on this.
od

Can you try with double \\ at the end? Because the back slash is used for escaping special characters, it is probably getting confused.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

OceanDeep Ten Centuries Points: 1078 More actions · Answer 10

Yep, double \\ works. The files are saved in the right path with the correct name.

Many thanks.....

od

bndgyawali SSC-Addicted Points: 413 More actions · Answer 11

can you please help me to pass the connection file in the same way. what will be the syntax of the connection file??

npetropoulos Valued Member Points: 61 More actions · Answer 12

Thanks, I was having the problem where the SQL Agent job had to pass the destination of the archive folder to the SSIS package at runtime. My syntax was /SET "\Package.Variables[User::varArchiveFilePath].Properties[Value];\\server\imports\sql exports\" and this was giving me an error. Your suggestion of the double slashes at the end fixed my problem. Now my syntax looks lke this /SET "\Package.Variables[User::varArchiveFilePath].Properties[Value];\\server\imports\sql exports\\"

rjimshaffer SSC Rookie Points: 38 More actions · Answer 13

I can see where you hard code the value of the variable. What if you don't know what the value you want you SSIS variable value is until run time?

I am using DTExec in a stored procedure to execute an SSIS package and use \SET to set my SSIS variable from a variable that I have declared in my stored procedure. I need a filedate and the stored procedure can be run multiple times if it was not run. (Example: Stored procedure didn't run Saturday or Sunday. On Monday a "catch up" will be run to get Saturday, Sunday and Monday. The filedate needs to represent the date of the data, so it changes each time the "catch up" process runs.

DECLARE @filedate VARCHAR(8)

SET @filedate = '20160521' (Changes to '20160522' the next run; changes to '20160522' the next run; and "today" as 20160523.)

DECLARE @cmd VARCHAR(1000)

SET @cmd = 'cd.. && "E:\Program Files\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /F "\\server\clientfolder\PachageName.dtsx"' /SET "\Package.variables[User::filedate].Value";@filedate

EXEC master.dbo.xp_cmdshell @cmd

Error on /SET

Does anyone know how this can be done?