CODEWITHSUNDEEP
ssisssis-2012
harnithu
harnithu

Reputation: 93

How to import null string as null mark in csv files using ssis package

How to import null string as null mark in csv files using ssis package 

csv file have values : 
id |name |deptno | sal
1  |a    |NULL   |NULL
2  |b    |NULL    |NULL
3  |c    |NULL    |NULL
NULL|d   |10      |NULL

type of file : csv comma separated values enter image description here

[![enter code here][2]][2]


Target table emp : 
id |name |deptno | sal
1  |a    |NULL   |NULL
2  |b    |NULL    |NULL
3  |c    |NULL    |NULL
NULL|d   |10      |NULL

Here i am not getting db null values and its getting string null values enter image description here enter image description here

how to resolve null values issue in ssis package side

Upvotes: 0

Views: 582

Answers (1)

Eric Brandt
Eric Brandt

Reputation: 8101

I don't have an SSDT installation handy, so I can't add any screenshot for this, and the expressions will just be "close". They'll likely need some tweaking to work.

In your Data Flow Task, between the source and the destination, add a Derived Column Transformation. Add two new columns. Call them something similar to the two columns you're having trouble with; say dept_no_clean and sal_clean. Leave them set to <Add as new column>.

For the Expression, you'll want to tell SSIS to convert the string value 'NULL' to an actual NULL, otherwise, just pass the numeric value (I'm guess at data types here. You'll want to set them to be whatever is correct for your data. I'll use an INTEGER for dept_no and a decimal for sal.):

sal_clean      sal="NULL" ? NULL(DT_NUMERIC, 18, 2) : (DT_NUMERIC, 18, 2)sal

dept_no_clean  dept_no="NULL" ? NULL(DT_I4) : (DT_I4)dept_no

The expression is an IF/THEN/ELSE conditional. And, again, might not be 100% correct as written.

Finally, edit your destination. Map the two new, derived columns to the corresponding columns in the destination table.

Upvotes: 0

Related Questions