?
Solved

Passing data variable to SSIS though command line

Posted on 2009-04-20
11
Medium Priority
?
1,889 Views
Last Modified: 2013-11-10
Hi,

I am trying pass the date of the day of execution to a SSIS variable when executing the package through a bat file

This is what i am doing

dtexec /FILE "C:\Package.dtsx" /set "\package.variables[ExecutionDate].Value";%date%

And i am getting an error as follows

Option "20-04-2009" is not valid.

This tells me that it is understand this date as an option in the command line execution...

Any ideas what this is happening...

Thanks



0
Comment
Question by:justin_smith
  • 5
  • 5
11 Comments
 

Author Comment

by:justin_smith
ID: 24183477
by the way, it works when i do the follwoing

dtexec /FILE "C:\Package.dtsx" /set "\package.variables[ExecutionDate].Value";"20-04-2009"
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 24184059
What type of variable. I started to assume a Date variable, but is it a string?
0
 

Author Comment

by:justin_smith
ID: 24188901
Its not string.. it is a datetime type
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:justin_smith
ID: 24188910
whats interesting is that the above with %date% worked on my development machine..--XP 64 bit

But when i moved it to XP 32 it didnt...

I am a little confused
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24191416
Are you intending to use the %DATE% reserved dos variable? Is there any reason you don't want to use a function internal to SSIS to work otu the date? is it because you want to be able to run the package for any date?
When I type
ECHO %DATE%
into a DOS prompt I get
Tue 21/04/2009
How about you?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24191446
I guess the real test is to go to a DOS prompt and type
ECHO dtexec /FILE "C:\Package.dtsx" /set "\package.variables[ExecutionDate].Value";%date%
and see how it expands. You might find it looks like this:
dtexec /FILE "C:\Package.dtsx" /set "\package.variables[ExecutionDate].Value";Tue 21/04/2009
which would explain why it thinks that  the date is a seperate parameter (formatting aside)
 
0
 

Author Comment

by:justin_smith
ID: 24191830
nmcdermaid:
excellent

the reason i am passing date is to run SSIS on any date...

any workaround to pass date in the expected format...
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24192292
How did the echo comde out (as per comment 24191446) ?
How do you intend to run it for a different date? will you build a manual command line and run it manually?
If so, then I suggest you alter your package so that if nothing is passed in for this variable, it automatically assigns it with todays date.
That way, using a command line without setting the parameter performs default behaviour (run for todays date), and a command line with an explicit parameter value will perform it for a given date.
That way you can avoid all that DOS command line hassle - its really not worth the hassle!! Someone in the DOS zone could cook you up an enormous unintelligible inline string to do it, but it really doesn't do much for 'self documentation'
 
However if you want to be able to run this for other dates in other ways, can you let me know how you want to do that.
 
0
 

Author Comment

by:justin_smith
ID: 24193409
///How do you intend to run it for a different date? will you build a manual command line and run it manually?
yes..

Do you want want me to evaluate the variable as expression to GETDATE()?
0
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 2000 total points
ID: 24200216
How did the echo command come out?
Do you want want me to evaluate the variable as expression to GETDATE()?
What I think you should do is put a step early on in the SSIS that checks the contents of the ExectuionDate Variable, and if it is blank, use an expression to load it up with the current date in the correct format.
In that way the package is consistent - it always runs off the contents of the ExecutionDate variable. it just happens that if a value is not supplied, it is populated with the current date.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 24210733
For anyone who is interested, the echo command indeed comes out as
dtexec /FILE "C:\Package.dtsx" /set "\package.variables[ExecutionDate].Value";Thu 23/04/2009  
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question