Data type conversion problem in derived column transformantion
I have a variable at the Package level MaxDate with string datatype in the expression of variable I am getting Max date from Time dimension.
Here's my expression for the variable .
@Maxdate =select max(date) from dimtime
Now I am using this variable in derived column transformation like this
rmrsentout < (DT_DBTIMESTAMP)" 1899-12-31" || rmrsentout > (DT_DBTIMESTAMP) @[User::Maxdate] ?
(DT_DBTIMESTAMP)"1899-12-31" : rmrsentout
and getting following error:
[Derived Column [10059]] Error: Error code 0x80020005 occurred attempting to convert from data type DT_WSTR to data type DT_DBTIMESTAMP.
but If use hardcoded a value like
rmrsentout < (DT_DBTIMESTAMP)" 1899-12-31" || rmrsentout > (DT_DBTIMESTAMP) "2200-12-31 "?
(DT_DBTIMESTAMP)"1899-12-31" : rmrsentout
it works I dont understand why its giving error when I replace it with the variable ?
arun
May 11th, 2011 7:11pm
Have you inspected that Variable to ensure that it is holding a valid date string?
Craig Bryden - Please mark correct answers
Free Windows Admin Tool Kit Click here and download it now
May 11th, 2011 7:25pm
Yes the data type of variable is stringarun
May 11th, 2011 7:35pm
I was actually asking if you have looked at what the contents of it is after you have populated it, to ensure that the VALUE is a valid datetime value.
Craig Bryden - Please mark correct answers
Free Windows Admin Tool Kit Click here and download it now
May 11th, 2011 7:37pm
You also need to ensure that if it is a valid datetime value that it is in the correct formatJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
May 11th, 2011 7:48pm
Thats the problem I am not getting the value . i am getting this
select max(date) from dimtime
how can I get the actual value in a varaiablearun
Free Windows Admin Tool Kit Click here and download it now
May 11th, 2011 9:29pm
A Derived Column Transformation cannot execute @Maxdate = select max(date) from dimtime. This is a T-SQL query.
There are a number of different ways to acheive what you are trying to do so I will suggest
one way that will require minimal changes to your existing package.
You need to use a Execute SQL Task (Controll Flow) to execute
select max(date) from dimtime and assign the returned value to
@[User::Maxdate].
Have a read of http://www.sqlis.com/post/The-Execute-SQL-Task.aspxJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
May 11th, 2011 9:50pm
I figure it out thanks !arun
Free Windows Admin Tool Kit Click here and download it now
May 11th, 2011 9:50pm
Hi Arun
What was actually the problem?Craig Bryden - Please mark correct answers
May 11th, 2011 9:52pm
He was trying to execute @Maxdate = select max(date) from dimtime. in a Derived Column Transformation. See my last postJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
Free Windows Admin Tool Kit Click here and download it now
May 11th, 2011 9:53pm
Sorry Jeff, but I don't read it that way. I quote from the original post:
"Here's my
expression for the variable .
@Maxdate =select max(date) from dimtime
Now I am using this variable in derived column transformation like this
rmrsentout < (DT_DBTIMESTAMP)" 1899-12-31" || rmrsentout > (DT_DBTIMESTAMP) @[User::Maxdate] ?
(DT_DBTIMESTAMP)"1899-12-31" : rmrsentout
and getting following error:"
Maybe Arun can clarify what was going wrong
Craig Bryden - Please mark correct answers
May 11th, 2011 9:57pm
Jeff is right I am not using the Execute sql task . I was directly giving the expression to the variable.arun
Free Windows Admin Tool Kit Click here and download it now
May 11th, 2011 9:58pm
True. It is difficult to ascertain from the OP's original post how he is populating his variable.
Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
May 11th, 2011 9:59pm
Jeff is right I am not using the Execute sql task . I was directly giving the expression to the variable.
arun
Glad you got it working in the end :-)Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
Free Windows Admin Tool Kit Click here and download it now
May 11th, 2011 10:00pm
Oh ok. In that case Jeff's answer should be marked as the answer :)
Craig Bryden - Please mark correct answers
May 11th, 2011 10:00pm
Thanks jeff and Craig.arun
Free Windows Admin Tool Kit Click here and download it now
May 11th, 2011 10:38pm
No problem and thanks for setting your question as answered.Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
May 11th, 2011 10:38pm