Welcome to ask SQL Server Central

Question

0

I have a variable called RouteCallerKeyDay and it takes a value such as '149421' which is calculated with the expression 148960 + DATEDIFF(dd, '2008-11-03 00:00:00', '2010-02-07 00:00:00') where 2010-02-07 00:00:00 is the import day I require.

The only way I have been able to assign it to my variable is if I run a script task with the following code in...

Dts.Variables("User::RouteCallerKeyDay").Value = CInt(148960 + DateDiff("d", "2008-11-03 00:00:00", Dts.Variables("User::DataImportDate").Value.ToString))

How do I set this up to work directly in the variables? I had to CInt() the whole value do I have to do something similar for the expression value?

flag	asked Feb 8 at 13:08 Ian Roke 948●2●9

Jay Bonk · Accepted Answer · 2010-02-08 14:36:00Z UTC

To get this to work, I created a variable called ImportDate, as you did, and set it to the current date for testing.

Next I created a variable RouteCallerKeyDay, which I defined as Int32. Next I clicked on the properties for the variable and set EvaluateAsExpression to TRUE, and then added the expression:

148960 + DATEDIFF( "d",  (DT_DBDATE) "2008-11-03", @[User::ImportDate] )

I assumed that the 148960 and 2008-11-03 were static. And that set the variable to 149422 (with today being 2010-02-08)

Why can’t I set this expression to a variable in SSIS yet I can assign the value with a script task?

1 Answer