January 11, 2012 at 11:26 am
I have SSIS to import from flat file to sql db, the flat source file has a column that may have blank values in it. The column in SQL table is a varchar , and can be nullable.
I checked return null values in source file as null in data flow, but when imported it still is empty string, I suppose by checking the setting, it should be Null in the value.
What could be wrong?
January 11, 2012 at 11:33 am
Please post the code that does the checking and transform.
January 11, 2012 at 11:42 am
Text files would need the 'NULL' character (I forget what it is offhand, it's so rarely used) included in it to return a real NULL. Otherwise it's 0 length strings.
Best bet is to use a derived column that converts 0-lengths to a DT_NULL. There's converter functions in the expression box you can just copy down.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 11, 2012 at 12:07 pm
Thanks, so I would like confirm: only if there are Null character in the flat file , the return nulls will work, correct?
My thought ealrier is blank values will be regarded as null values at backgroud and will imported as null character into SQL table.
January 11, 2012 at 12:10 pm
sqlfriends (1/11/2012)
Thanks, so I would like confirm: only if there are Null character in the flat file , the return nulls will work, correct?My thought ealrier is blank values will be regarded as null values at backgroud and will imported as null character into SQL table.
No, unfortunately. Nulls and 0-length strings are definitively different items, and thus, a special character was born. 0-length strings are a specific way of stating 'there is NOTHING entered here', whereas a NULL is 'Unknown/unentered value entered here'. Since you're actually handing it 0-length strings from the text file, that's how they're considered.
Another way to do it is to have the text generator actually include the word NULL so you can derived-swap it later rather than having it autodetect. Considering the NULL character itself is unreadable this is another common workaround, but you have to get the source data to modify its output.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 11, 2012 at 12:28 pm
Evil Kraig F (1/11/2012)
sqlfriends (1/11/2012)
Thanks, so I would like confirm: only if there are Null character in the flat file , the return nulls will work, correct?My thought ealrier is blank values will be regarded as null values at backgroud and will imported as null character into SQL table.
No, unfortunately. Nulls and 0-length strings are definitively different items, and thus, a special character was born. 0-length strings are a specific way of stating 'there is NOTHING entered here', whereas a NULL is 'Unknown/unentered value entered here'. Since you're actually handing it 0-length strings from the text file, that's how they're considered.
Another way to do it is to have the text generator actually include the word NULL so you can derived-swap it later rather than having it autodetect. Considering the NULL character itself is unreadable this is another common workaround, but you have to get the source data to modify its output.
Thanks much.
So, when you answer no, you mean no to my second statement, but Yes to my first statement,
Correct?
At this time we don't have control to generate the source file which is from another source, so they will still keep blank values instead of using character null. My only option is use derived column to do the conversion, is that right?
Thanks
January 11, 2012 at 12:54 pm
sqlfriends (1/11/2012)
Evil Kraig F (1/11/2012)
sqlfriends (1/11/2012)
Thanks, so I would like confirm: only if there are Null character in the flat file , the return nulls will work, correct?My thought ealrier is blank values will be regarded as null values at backgroud and will imported as null character into SQL table.
No, unfortunately. Nulls and 0-length strings are definitively different items, and thus, a special character was born. 0-length strings are a specific way of stating 'there is NOTHING entered here', whereas a NULL is 'Unknown/unentered value entered here'. Since you're actually handing it 0-length strings from the text file, that's how they're considered.
Another way to do it is to have the text generator actually include the word NULL so you can derived-swap it later rather than having it autodetect. Considering the NULL character itself is unreadable this is another common workaround, but you have to get the source data to modify its output.
Thanks much.
So, when you answer no, you mean no to my second statement, but Yes to my first statement,
Correct?
At this time we don't have control to generate the source file which is from another source, so they will still keep blank values instead of using character null. My only option is use derived column to do the conversion, is that right?
Thanks
Sorry, yeah. Yes to the first, no to the second. Derived column is almost always involved in cases where you're working from text files.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 11, 2012 at 2:28 pm
I tried it to convert it to null in a derived columns, it converted successfully,
but when inserting into database, it gives error: the data balue cannot be converted fro reasons other than sign mismatch or dataover flow, and I see those columns are those with nulls.
Thanks
January 11, 2012 at 2:33 pm
In the derived columns I also try this:
TRIM(PerNo) == "" || ISNULL(PerNo) ? 0 : (DT_I4)TRIM(PerNo)
it converts OK, and I see the data viewer has 0 values, why when it goes to Oledb destination table, it failed with those 0 values records.
The table column is Perno, data type is int, and nullable.
It fails with the message in my last post.
January 11, 2012 at 2:50 pm
Check the metadata from the outbound of the derived column component and make sure it's a compatable datatype. You may need to dictate the exact datatype you expect outbound in the expression.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply