CODEWITHSUNDEEP
sql-servert-sqlssisnulldts
Kam Mistry
Kam Mistry

Reputation: 105

allowing comma delimited csv file to have null value for import via dts package

I have a DTS package to import a CSV into my sql server table; the table has field Rate_Code with is defined as a nullable numeric field of type Real. I want my csv source to be able to define column RATE_CODE as NULL, because the field is nullable in the database. Packages works, but only for rows that don't have null values.

I tried the following csv's:

ACCT_NO,RATE_CODE,POLR_INDICATOR,RunId
0000130209,NULL,1,99

Error: Data conversion failed while converting column "RATE_CODE" (137) to column "RATE_CODE" (12). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

ACCT_NO,RATE_CODE,POLR_INDICATOR,RunId
0000130209,,1,99

Same error.

I need a csv that would work to be able to import null fields.

Upvotes: 1

Views: 2707

Answers (1)

Eric Brandt
Eric Brandt

Reputation: 8101

SQL Server can't implicitly convert a string value to a real data type, so you'll have to put an explicit transformation into your package to handle either the case of an empty string or the case of the text value 'NULL', which I've seen in plenty of flat files.

Inside your data flow, you'll want to add a derived column transformation in between your source and your destination to convert those missing values to an actual NULL of data type real.

This expression should get you where you want to be. Line breaks are for readability. Remove them and the comments for use in the package.

( (DT_WSTR, 1)[RATE_CODE] =="" ) ||      <-- If the string version is an empty string OR
( (DT_WSTR, 4)[RATE_CODE] =="NULL" ) ||  <-- If the string version is the word "NULL" OR
( ISNULL( [RATE_CODE] ))                 <-- If the column is actually NULL
?                                        <-- THEN
NULL(DT_R4)                              <-- Return a NULL of data type real
:                                        <-- ELSE
(DT_R4)[RATE_CODE]                       <-- Return the value of the column as a real data type

For your copying & pasting convenience:

( (DT_WSTR, 1)[RATE_CODE] =="" ) || ( (DT_WSTR, 4)[RATE_CODE] =="NULL" ) || ( ISNULL( [RATE_CODE] )) ? NULL(DT_R4) : (DT_R4)[RATE_CODE]

Edit: To use this code, open the package in Visual Studio/SQL Server Data Tools (SSDT). Open the Data Flow Task that the wizard created. Delete the arrow between the Flat File Source object and the OLEDB Destination object. Go to the SSIS Toolbox and drag a Derived Column Transformation object onto the work surface. Connect the Source to the Transformation. Open the Transformation.

In the lower box, under Derived Column Name, type Rate_Code. Under Derived Column, click on the drop down menu and select Replace "Rate_Code". Under Expression, paste the expression from above. The Data Type column should automatically change to float [DT_R4]. Close the Transformation editor by clicking OK.

Connect the Transformation to the Destination object. Open the Destination object. On the left, click on Mappings. Make sure the Rate_Code from the source is still mapped to Rate_Code for the destination. If it's not, map it (click and hold on the source, then drag over to the destination and release; a solid black line should appear.)

Save your package, then try executing it again.

Upvotes: 2

Related Questions