April 30, 2012 at 4:51 pm
I am trying to load a flat file using SSIS. The data in the flat file is something like this
id, name, status
1, Jassy,
2,, abcd
3, v'dejr, dkfal'sdfh
when I loaded then to the SQL Server, I am getting blank spots on the places where there is no data.
1 Jassy
2 abcd
3 v'dejr dkfal'sdfh
I want null instead, what could be done in the downstream to get it done. There are 28 columns in the actual file and I am trying to avoid update query after I load data.
Thanks
April 30, 2012 at 5:30 pm
Use a derived column that will check for blank spaces and insert the null version of the datatype into the column if that happens.
Basically, for each column, you'll do something like this as the expression (assuming it's an INT):
LTRIM(RTRIM( Column1)) == "" ? NULL(DT_UI4) : Column1
Use that as a replace columna and it'll include NULLs into the stream. For the source file to actually recognize NULL, they'd have to use the particular ANSI character that is considered it (I forget offhand) which is an unreadable character.
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
May 2, 2012 at 12:14 pm
Evil Kraig F (4/30/2012)
Basically, for each column, you'll do something like this as the expression (assuming it's an INT):LTRIM(RTRIM( Column1)) == "" ? NULL(DT_UI4) : Column1
What Evil Kraig says is spot on; one little nitpick, SSIS expressions has a TRIM() function that you can use to replace the LTRIM(RTRIM()). Something like:
[font="Courier"]TRIM(Column1)) == "" ? NULL(DT_UI4) : Column1[/font]
May 3, 2012 at 7:32 am
One other thing you can look at, althought I have not tested it for this purpose. In the flat file source editor on the connection manager tab there is a checkbox for "Retain null values from the source as null values in the data flow."
May 11, 2012 at 9:51 am
That idea by Dan should work.
June 6, 2012 at 5:14 am
I agree.. Use "Retain null values from the source as null values in the data flow" in flat file connection manager.:-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply