September 4, 2008 at 11:27 pm
Hi All,
in the flat file that we receive , sometimes we receive the value for datetime column as '00000000' (CCYYMMDD format) . If i get date value as 00000000 , then i should make that column value as null and insert into the table. Else , i will insert into that date value into that table.
i read the column as string(from the flat file), and assign into variable PROC_DT.
then i use the derived column transformation like this :
PROC_DT == "00000000" ? (DT_DBTIMESTAMP)NULL : (DT_DBTIMESTAMP)(SUBSTRING(PROC_DT,5,2) + "/" + SUBSTRING(PROC_DT,7,2) + "/" + SUBSTRING(PROC_DT,1,4))
But getting error message. Can any one help me in handling this?
Thanks,
Arunvijay
September 5, 2008 at 4:37 am
You can't cast a NULL value as a specific datatype. It's either NULL or it's not.
Secondly, you can't mix strings and datetime values in SSIS. So either pick a fake date for the NULL value or Derive your other datetimes as a string and convert them later. (MS uses 01/01/1900 for their default fake dates)
Lastly, for this to work properly, you'll want to make sure that your final destination column accepts NULL values.
PROC_DT == "00000000" ? "NULL" : (SUBSTRING(PROC_DT,5,2) + "/"
+ SUBSTRING(PROC_DT,7,2) + "/" + SUBSTRING(PROC_DT,1,4))
After this, you can use a T-SQL Convert or Cast to change the value to Datetime where it's not NULL. But SSIS won't allow mixing of datatypes in a Derived Column Task.
September 5, 2008 at 7:15 am
Brandie is right that you can't mix data types. I'd do this:
PROC_DT == "00000000" ? NULL(DT_STR, 8) : (SUBSTRING(PROC_DT,5,2) + "/"
+ SUBSTRING(PROC_DT,7,2) + "/" + SUBSTRING(PROC_DT,1,4))
The NULL(DT_STR) produces a null value of the correct data type, which will translate to SYSTEM.DBNULL.VALUE so a null gets inserted into the database.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 5, 2008 at 8:02 am
Jack,
That is a SWEET workaround for NULL. I didn't even know about it and I just happen to need it.
Thanks!
EDIT: And you know, as soon as I posted this, I went to try it out and it errored out on me.
"For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations".
Darnit. Nice try, though.
SECOND EDIT: WHOOPS. It has to be DT_WSTR, not DT_STR... WSTR works. @=) Thanks again.
September 5, 2008 at 8:23 am
Thanks all for ur replies.
I used the below transformation :
PROC_DT == "00000000" ? "NULL" : (SUBSTRING(PROC_DT,5,2) + "/" + SUBSTRING(PROC_DT,7,2) + "/" + SUBSTRING(PROC_DT,1,4))
This had no syntax errors , but the loading failed . The reason being , the database column is of "datetime" datatype , but the output of the derived column is "DT_STR" datatype.
How can i resolve this?
Thanks,
Arunvijay
September 5, 2008 at 8:37 am
As I said in my previous post, you're going to have do another convert.
You can try to do it all in one task, but you might need two Derived column tasks.
First, change the "NULL" TO NULL(DT_WSTR,10) so that the Conversion doesn't bomb out on the literal string "NULL".
Then see if this works:
(DT_DBTIMESTAMP)(PROC_DT == "00000000" ? NULL(DT_WSTR,10) :
(SUBSTRING(PROC_DT,5,2) + "/" + SUBSTRING(PROC_DT,7,2) + "/" +
SUBSTRING(PROC_DT,1,4)) )
September 5, 2008 at 10:00 am
September 6, 2008 at 1:14 am
Thanks Folks. The solution works gr8
December 10, 2008 at 8:47 am
Hi,
I am having a similar problem, except when I imported the rows, it will check to see if the value is null and if it is, then it will assign it a predetermined value.. like this.. the other part in there checks if it's out of range and uses a different value for that..
If Not rowValues(1).IsNullOrEmpty(rowValues.GetValue(1).ToString) Then
Row.CholesterolResultDate = DateTime.Parse(rowValues(1).ToString())
If DateTime.Parse(rowValues(1).ToString()) = CType("12/31/9999 11:59:59 PM", DateTime) Then
Row.CholesterolResultDate = CType("1/1/1900 12:00:00 AM", Date)
End If
Else
Row.CholesterolResultDate = DateTime.Parse("01/01/1901")
so if I were going to use a derived column, then it would need to check for that specific date ( 01/01/1900 ) . Any suggestions for getting it to goto dbnull.value on the wrote to the db..
write now it's using a transform script to write to the db,, and in there it checks this for the date.. if it's 1901 then it will assign the parameter like this:
oCmd.Parameters.Add("@BloodPressureDate", SqlDbType.DateTime).Value = DBNull.Value
but I am trying to remove that validation to earlier in the project so that I can run a faster write script to the db.. maybe a oledb destination with the fast load option..
December 10, 2008 at 8:57 am
ok,, I came up with this as a possible for the derived column expression..
YEAR(CholesterolResultDate) == 1901 ? NULL(DT_DATE) : CholesterolResultDate
I haven't checked it running yet, but I thought I'd post that..
February 25, 2009 at 9:43 am
Thanks!...This helped me!
February 25, 2009 at 10:33 am
It's always good to see the "legacy" effects of old threads. @=)
Glad it helped.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply