October 17, 2011 at 9:48 am
When I pull data from DB2 to Sql, I see there is Date column which has NULL value in it. I couldn't help myself after going through books online.. Tried ISNULL(column)?"UNKNOWN vlaue":column, but doesn't work.
Destination column has smalldatetime as a data type... please advise me and let me know plz incase any inputs ...
October 17, 2011 at 9:53 am
quillis131 (10/17/2011)
When I pull data from DB2 to Sql, I see there is Date column which has NULL value in it. I couldn't help myself after going through books online.. Tried ISNULL(column)?"UNKNOWN vlaue":column, but doesn't work.Destination column has smalldatetime as a data type... please advise me and let me know plz incase any inputs ...
Not sure what your question is. What is it you are trying to do?
ISNULL(column)?"UNKNOWN vlaue":column
I think maybe you are trying to use a default when the date is null?
The proper syntax for that would be
ISNULL(column, somedatevalue)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 17, 2011 at 10:55 am
Need to extract DATE column to SQL DB (SMALLDATETIME) from DB2. But Source columns has null values ( empty values).... This Date column may also have DATE mentioned for some rows. By Derived transfromation should substitute in case of any Null coming from Source ..otherwise just send the value in source to Destination...
October 17, 2011 at 10:57 am
Can you just make the destination table not allow nulls and add a default value?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 17, 2011 at 11:01 am
If you're using ISNULL in SSIS, then the way you need to use it for your purposes is something like:
ISNULL(column) ? (VALUE_IF_TRUE) : (VALUE_IF_FALSE)
So, for example,
ISNULL(column) ? "1950-01-01" : column
Since the value for both true and false need to evaluate to the same data type.
October 17, 2011 at 11:09 am
kramaswamy (10/17/2011)
If you're using ISNULL in SSIS, then the way you need to use it for your purposes is something like:ISNULL(column) ? (VALUE_IF_TRUE) : (VALUE_IF_FALSE)
So, for example,
ISNULL(column) ? "1950-01-01" : column
Since the value for both true and false need to evaluate to the same data type.
Tried out this ISNULL(column) ? "1950-01-01" : column
But my source column value doesn't get update to 1950-01-01
October 17, 2011 at 11:15 am
What do you mean by that, exactly? You're saying your source has a NULL value, and in your destination, you get an error saying you can't write a NULL value to that column? Or in the destination, the NULL value gets written instead of the date "1950-01-01"?
Also, are you sure that it is a NULL value in your source, and not the string "NULL"?
October 17, 2011 at 12:39 pm
kramaswamy (10/17/2011)
What do you mean by that, exactly? You're saying your source has a NULL value, and in your destination, you get an error saying you can't write a NULL value to that column? Or in the destination, the NULL value gets written instead of the date "1950-01-01"?Also, are you sure that it is a NULL value in your source, and not the string "NULL"?
I am Sorry for confusion...My destination is not getting updated with the dummy date.
October 17, 2011 at 12:48 pm
What is it getting instead? The NULL value?
October 17, 2011 at 1:36 pm
kramaswamy (10/17/2011)
What is it getting instead? The NULL value?
YES....
October 17, 2011 at 1:53 pm
Try putting a data viewer right after your derived column transformation, and then view the data exiting that transformation. See if the derived column you've created contains the dummy date value that you want.
October 17, 2011 at 2:14 pm
kramaswamy (10/17/2011)
Try putting a data viewer right after your derived column transformation, and then view the data exiting that transformation. See if the derived column you've created contains the dummy date value that you want.
Yaa thats what excatly I did ... and got to know that column is not updated with any kind of Dummy date incase of NULL
October 17, 2011 at 2:29 pm
I guess this goes back to the original question, is it a true null value or a string saying 'null'.
October 18, 2011 at 2:56 pm
kramaswamy (10/17/2011)
If you're using ISNULL in SSIS, then the way you need to use it for your purposes is something like:ISNULL(column) ? (VALUE_IF_TRUE) : (VALUE_IF_FALSE)
So, for example,
ISNULL(column) ? "1950-01-01" : column
Since the value for both true and false need to evaluate to the same data type.
Hi kramaswamy... Its working finally Thank you, Probably I was missing double quotes.
I have another question with this .....
I tried ISNULL(column) ? "NULL" : column... this goes fine at Source but my mapped destination column says SMALLDATETIME data type, I made sure placed a DATA CONVERSION task b/w Derived column Transformation and Destination and convert Data type to smalldata time
But it Fails again with error message " The value could not be converted because of a potential loss of data."... I know this is something to data type coversion... can you help me in this please
October 19, 2011 at 4:40 am
your source column is SMALLDATETIME, so you can't write the string "NULL" to it.
This expression:
ISNULL(column) ? "NULL" : column
has two data types it is attempting to evaluate to - a string and a dt_dbtimestamp.
What you should have is this:
ISNULL(column) ? NULL(DT_DBTIMESTAMP) : column
OR
ISNULL(column) ? "NULL" : LEFT((DT_WSTR, 30) column, 11)
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply