Populating DateTime variable from Execute SQL task

Question

Populating DateTime variable from Execute SQL task

Ulysses Bear

SSC Veteran

Points: 209
More actions
September 27, 2006 at 10:44 am

#153202

Please help!
I am designing an SSIS package and need to change the value of a DateTime global variable with the value returned from an Execute SQL task. The queary is as follows:

Select Versiondate = dateadd(dd,+1,Max(CreateDate)) from msdb..sysdtspackages p with(nolock)
where name = '@name'

The error message is as follows:
SSIS package "Package.dtsx" starting.
Error: 0xC002F210 at VersionDate_Set, Execute SQL Task: Executing the query "Select Versiondate = dateadd(dd,+1,Max(CreateDate)) from msdb..sysdtspackages p with(nolock)
where name = '@name'" failed with the following error: "The type of the value being assigned to variable "User::VersionDate" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: VersionDate_Set
SSIS package "Package.dtsx" finished: Success.
The variable is set to data type DateTime, but the value will not change. Any ideas?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Iordan Slavov SSChasing Mays Points: 616 More actions · Answer 1

It seems to me you should use a parametric query, i.e. replace the variable by a question mark (?), and then map your variable to a parameter (say 0). This all is done in the Editor of the SQL Task, "Parameter mapping".

Also, I am not sure you can put in the "SQL Statement" of the SQL Task any script that runs in the Query designer - if you declare variables (like @name), set them (with SET @name=N'something') and then try to use the variable in your query it may not find it. That's why I put this kind of variables in a temporary table and my SQL task is of ONLY queries which read varaibles from the temp. table(s).

Look here:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=148&messageid=299008#bm309772

Just to be on the safe side - cast all dates into strings. You always can recover the datetime from a string with the reverse cast.

Ulysses Bear SSC Veteran Points: 209 More actions · Answer 2

When I cast dates into strings, do I do this via an expression?

Iordan Slavov SSChasing Mays Points: 616 More actions · Answer 3

I meant using something like

select ..., CAST(datevalue AS varchar(10)) as strDate, ...