Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: How to pass DateTime parameters to a package via dtexec

Update 2nd Nov 2005: I've had a notification from MS today that this has been fixed. Expect the fix to be in SP1.

-Jamie

 

DTExec provides us the ability to set any property of any variable or control-flow task via the command-line, therefore overriding whatever value is hardcoded into the package.

I've had great fun (!!!) today trying to supply values into a variable of type DateTime and then using that value in the WHERE clause of a SELECT query in an OLE DB Source Adapter. i was building the SELECT statement using an expression. I've come with some guidelines that you should follow if you want to try and do this yourself.

Ostensibly you can pass any value into a DateTime variable from DTExec. You can prove this by passing in the value "foo" and then outputting the value of the variable in a message box - sure enough the DateTime variable will contain the value "foo". That's weird isn't it? It seems (I may be wrong) as though internally the DateTime variable is stored as a string which is inherently different to how SQL Server stores a datetime value. Well I don't like it anyway...I would have preferred it if you could only supply legitimate datetime values.

Of course, as soon as you try and use a DateTime variable in any sort of useful way it will fail if you don't have a valid datetime value in there. And that's where my problems started. I needed to supply a datetime value that could be used in a an expression to build my SELECT statement but seemingly whatever value I passed in, NOTHING would work. Not even "DD/MM/YYYY HH:MI:SS" which is what the value will appear as in a configuration file if you place a configuration on the variable value.

This puzzled me. No I'll rephrase that, it infuriated me. I wasted alot of time trying to get this to work and eventually I stumbled on the answer. If you need to use DateTime variable (for which you are supplying a value via DTExec) in an expression then you need to parse the DateTime variable as a date (i.e. DT_DATE) in order to use it.

Here's the expression syntax for doing that:

(DT_DATE) @[namespace::variable_name]

That seems ridiculous to me. Why do you need to cast a variable of type DateTime into a DateTime value in order to use it?

Don't say I didn't warn you!!! :)

One other thing, in the course of my struggles I tried many different formats for the value passed in from the command line. These are the ones that worked:

  • "DD/MM/YYYY HH:MI:SS AM"
  • "DD/MM/YYYY HH:MI:SS PM"
  • "DD/MM/YYYY"
  • "DD/MM/YYYY HH:MI:SS"
  • "DD-MM-YYYY"
  • "DD/MM/YYYY HH:MI AM"
  • "DD/MM/YYYY HH:MI PM"
  • "DD/MM/YYYY HH:MI"
  • "YYYY-MM-DD HH:MI:SS"
  • "YYYY/MM/DD HH:MI"

and here's the one that didn't:

  • "YYYYMMDD HH:MI"

OK, I know there's no american format dates in there but I'm english and I'm working on a UK project in the UK (admittedly for an american company) so what can I say...bite me!!! :)

If there is another, easier, way of doing this then I'm all ears!

-Jamie

 

 

Published Tuesday, October 11, 2005 1:43 PM by jamie.thomson

Comments

 

Kristian Wedberg said:

Jamie,

A quick test shows that when setting variables from the command line with /SET (or using the corresponding functionality in dtexecui), you’re in essence creating a new variable with the same name and path as the one in your package, but with the String data type. This is true not only when the original package variable is a DateTime variable, but also for the other types (Int32…)

I used the following script code to verify that a variable “datevar” changed type when set from the command line, use the path \Package.Variables[datevar] when setting it with dtexec(ui):

MsgBox("Value = " + Dts.Variables.Item("datevar").Value.ToString() + ", " + _
"Type = " + Dts.Variables.Item("datevar").Value.GetType().ToString() + ", " + _
"Path = " + Dts.Variables.Item("datevar").GetPackagePath() + ", " _
)

Running this shows that irrespective of the initial variable data type, it will be a String when setting datevar from the command line, even though the path is the same as before (i.e. it overwrites the original variable, it doesn’t just hide it.)

I fully agree that this is counter intuitive - maybe an enhancement request to MS to have a /SET2 option that automatically converts to the target variable data type?

As long as it works like above though, I’d make sure that any variables meant to be overwritten from the command line will be of the String data type, the potential for errors if the data type changes is just too great otherwise.


Cheers!Kristian
October 22, 2005 10:51 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

Remember how I was whinging a couple of weeks ago about how I was having difficulty setting the value...
October 24, 2005 10:20 PM
 

Professional Association for SQL Server (PASS) SIG said:

May 3, 2006 5:03 PM
 

SSIS Junkie said:

On 11th October 2005 I wrote a blog entry in which I informed about a bug in SSIS. The problem was that

January 16, 2007 6:08 PM
 

SSIS Junkie said:

Remember how I was whinging a couple of weeks ago about how I was having difficulty setting the value

January 16, 2007 6:14 PM
 

Mikayla said:

Wow you just saved me some headache--I was trying to pass a datetime value to my SSIS package and it wouldn't work. At least I have another way I can do it without using datetime....

February 22, 2007 10:11 PM
 

Prabha said:

Thank you very much! You saved me from spending some more time to fix the issue.

June 7, 2007 12:02 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems