January 31, 2012 at 2:16 pm
i have flate file that i need to put in sql table.now this file looks like
col1 col2 col3 col4
1 NA 0 2
2 3 4
3 3 NA
4 4 NA 3
total 24 23 34 45
I NEED TO PUT 0 where there is blank and NA,how to do that in ssis and how to handle last row(total),Please explain me.
I tried to dot it with derived column,but donnt know how to do it
January 31, 2012 at 11:25 pm
Derived column expression:
(myColumn == "NA" || myColumn == "") ? (DT_STR,2,1252) 0 : myColumn
You probably need to keep it as a string datatype, as blank and NA are strings, not numbers. You can convert the data to numeric later on.
You can get rid of the total line (if that is your question) with a conditional split:
col1 = "total"
Do not map the output of this expression and you got rid of the row.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 2, 2012 at 8:29 am
check http://www.sql-server-performance.com/2007/import-text-files-ssis/
it has 3 pages readd ALL of them specially page 3
and one problem you will have is
if you have ...
Row 1--> 1,2,3,4
Row 2--> 1,2
Row 3--> 1,3
My question is in row 2 how will you know the 3rd and 4th field are missing?
it can be
The letter X is that it is NULL and you mentioned that you wan to convert it to 0
Row 2--> 1,2,X,X
Row 2--> X,X,1,2
Row 2--> X,1,X,2
Row 2--> 1,X,X,2
Row 2--> 1,X,2,X
Row 2--> etc.....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply