October 25, 2011 at 1:33 pm
Hi,
I've got a package set up to load csv files, which works great EXCEPT when I have an NA value in one of my numeric columns. This specific column I'm looking at today is defined as a FLOAT, but I have other instances where the numeric column may be defined as an INT. The table in the DB is set up so that it allows for NULL values, but somehow the package seems to be failing because I cannot get it to recognize that NA should map to NULL.
How do I create this mapping within my SSIS package? Hopefully it is a relatively easy fix as changing the csv files that are to be loaded is quite a bit more of an arduous task.
Thanks in advance,
Brigid
October 25, 2011 at 1:40 pm
The value needs to be of a character type so you can do a derived column on the text value NA. I'm guessing it is failing at the source, the records with NA aren't even getting into the pipeline and the source is turning red.
CEWII
October 25, 2011 at 2:06 pm
Okay - so I can go back into the SSIS package and set that column to a Unicode string with no text qualifier (as the NA's don't have quotes around them)... Now the Advanced Editor for my source csv file shows that column with data type DT_WSTR.
But then how do I go about transforming the NA text to a NULL when I am converting the whole column to numerics? I'm looking around and the closest I'm getting seems to be the Derived Column Transformation Editor, and the REPLACE function under the String Functions folder. But when I get to this stage and try:
REPLACE(mycol, "NA", "")
I get an error where it thinks mycol is still of data type DT_R4 (it still thinks that it's a float). Do I have to change the source column definition in more than one place? Or am I going down the wrong path entirely?
Thanks again!
-Brigid
October 25, 2011 at 2:09 pm
Double click on the line connecting the source from the derived column, it will tell you what the data type coming out of the source is, you might have to tweak it in more than one spot in the source.
CEWII
October 25, 2011 at 2:35 pm
Derive the column that you've swapped to characters as a replacement for itself, and setup an if statement:
[Column] == "NA" ? NULL(DT_WSTR, «length») : [Column]
Keep it as WSTR for the moment as you do that.
Next, add in a conversion to take the Unicode String and turn it into the float/int you need.
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
October 26, 2011 at 8:09 am
I ended up having to create a new connection to the csv file, but then the if statement worked.
Thanks!
October 27, 2011 at 12:14 pm
bkmooney (10/26/2011)
I ended up having to create a new connection to the csv file, but then the if statement worked.Thanks!
My pleasure, happy to help. 🙂
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 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply