Welcome to EMC Consulting Blogs

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