September 27, 2006 at 10:44 am
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?
September 27, 2006 at 3:57 pm
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.
October 4, 2006 at 2:06 pm
When I cast dates into strings, do I do this via an expression?
October 6, 2006 at 12:59 pm
I meant using something like
select ..., CAST(datevalue AS varchar(10)) as strDate, ...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply