Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Setting variables via the command line

Remember how I was whinging a couple of weeks ago about how I was having difficulty setting the value of a Datetime variable via dtexec (http://blogs.conchango.com/jamiethomson/archive/2005/10/11/2261.aspx)? Well it appears my whinging was warranted. Andrea Fox from the SSIS documentation team picked this up and, together with gurus Mark Durley and Sergei Ivanov from the dev team, has confirmed that this really is a problem (argubly a bug).

Also (as Kristian pointed out http://blogs.conchango.com/jamiethomson/archive/2005/10/11/2261.aspx#2295) its not limited to datetime variables - its all of them.

The hard and fast rule is if you want to use a variable that is populated from the command line in an expression then you have to cast it to the value that you need it as EVEN if the variable is of the correct type at design-time. Here's the example that Andrea provided:

"The way to fix this is to put an explicit cast around variables in an expression, to cast them to the type you want."

For example, if you write:

@[User::NumWidgets] == 1

and @[User::NumWidgets] was populated from the command line it really needs to be written as:

(DT_14)(@[User::NumWidgets] ) == 1

OK, so this isn't great news...but its not all bad. It seems as though this is going to become a high priority for Microsoft and they hope to have it fixed in time for the first service pack.

Credit to Andrea for getting this actioned. And even though it would be preferable that this bug didn't occur its great to see MS listening to their customers' greivances and getting stuff sorted ASAP. Just don't do it again that's all :~)

-Jamie

 

 

Published Monday, October 24, 2005 10:06 PM by jamie.thomson

Comments

No Comments
New Comments to this post are disabled

This Blog

Syndication

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