May 27, 2011 at 3:47 pm
Hello all,
Im using SSIS to try to import a text file...When I first set it up, I got an error when I ran it and the error was that the data conversions did not work properly and truncating data would cause data loss...So I went to my text file connection manager and changed all the data types and length to match what they would be in the corresponding table...The text file was just reading every column as a string with a varchar(50)...So now the text file has the correct lengths and data types (mostly contains varchars with some datetimes in it as well)...
So when I mapped everything up, and ran it again here is the error I received...
[Flat File Source [17346]] Error: Data conversion failed. The data conversion for column "Column 37" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
After googling the error code, I found somebody posted that I should check the box in the Flat File Source that says "Retain null values from the source as null values in the data flow"...So I checked it and ran the package again and it still failed...
What is happening is there are blanks in the data file where the datetime field is and so SSIS is reading it as a blank string...So can I import NULL for that field where the row is blank or how do I handle that?
Thanks in advance...
May 27, 2011 at 7:08 pm
Here's where I would start:
1) Import it as a string from the text file.
2) Use the Derived Column transformation to change columns with blanks to NULL.
3) Use the Data Type Conversion transformation to change the column data type to match your database column.
Then you can move that data into a DB destination with proper data types.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 27, 2011 at 11:50 pm
Thank you...
Will try that when I return to work...
May 31, 2011 at 9:11 am
opc.three (5/27/2011)
Here's where I would start:1) Import it as a string from the text file.
2) Use the Derived Column transformation to change columns with blanks to NULL.
3) Use the Data Type Conversion transformation to change the column data type to match your database column.
Then you can move that data into a DB destination with proper data types.
What is the correct expression to use when using the Derive Column Transformation to change columns with blank to NULL?
May 31, 2011 at 9:38 am
It depends on the type of incoming string but here is what it will look like for DT_STR 100 with 1252 encoding:
LTRIM(ColumnName) == "" ? (DT_STR,100,1252)NULL(DT_STR,100,1252) : ColumnName
Reference:
SSIS NULL function: http://msdn.microsoft.com/en-us/library/ms141758.aspx
SSIS ternary operator: http://msdn.microsoft.com/en-us/library/ms141680.aspx
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 31, 2011 at 9:50 am
Here is what I have as my package:
In Data Flow:
Step 1 - Flat File Source (text file - all column types are string) Also checked the box to retain null values
Step 2 - Derived Column - For 10 fields out of the 127, in the database, these fields are datetime...For about 3 of those 10 fields, the text file contains a blank string - Guessing these blank strings need to be converted to NULL so the database will accept them...I used the Derive Column Transformation to try to handle that and here is the expression I used (may be wrong):
"IsNull([Column 37]?NULL(<string> : [Column 37]"
Step 3 - Data Conversion - For those 10 fields that have string data types in the Input, I select datetime as the output...
Step 4 - OLE DB Destination - I select my table the file needs to be loaded and I map the columns accordingly except for those 10 columns I use Data Conversion.Column 37 as my input...I highlight my mouse over each column and check to make sure that both Input and Output columns have same data type...
I run the package and it fails with this error:
[Data Conversion [9]] Error: Data conversion failed while converting column "Column 37" (195) to column "Column 37" (904). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
How do I fix this? Am I using the wrong expression to replace the blank strings to NULL?
May 31, 2011 at 10:10 am
I got it...Thanks however instead of LTRIM I just used TRIM...
May 31, 2011 at 10:34 am
Happy to assist 🙂
As an aside, LTRIM and TRIM and RTRIM run against a string with all spaces will yield the same result...and TRIM may have additional overhead since it essentially runs an RTRIM and LTRIM operation under the covers.
I am not sure if RTRIM or LTRIM will outperform one another, it may depend on the dataset, but consider using one of those instead of TRIM to save some cycles.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply