February 27, 2014 at 11:49 am
Hi,
I am reading a bar (|) separated text file and pushing data to SQL Server table. I am getting issues while reading last column in the source file. Basically last column will have date value (in format 02/28/2014) and destination column datatype is smalldatetime.
In source file I'm reading that column as DT_STR, length 10.
I have a derived column that reads that column as below
ISNULL(Foreclosure_Date) ? (SUBSTRING(NonCashAjmt,5,2) + "/" + SUBSTRING(NonCashAjmt,7,2) + "/" + SUBSTRING(NonCashAjmt,1,4)) : (SUBSTRING(Foreclosure_Date,5,2) + "/" + SUBSTRING(Foreclosure_Date,7,2) + "/" + SUBSTRING(Foreclosure_Date,1,4))
So here I'm just reading 10 char in that column only. When I run the package, it throws below error:
[Source_FF_CABQ_EscrowFile [1]] Error: Data conversion failed. The data conversion for column "Foreclosure_Date" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[Source_FF_CABQ_EscrowFile [1]] Error: The "output column "Foreclosure_Date" (182)" failed because truncation occurred, and the truncation row disposition on "output column "Foreclosure_Date" (182)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
[Source_FF_CABQ_EscrowFile [1]] Error: An error occurred while processing file "C:\Investor Services\\CABQ_Escrow_cXXX" on data row 1.
Okay, Now as the error says text was truncated so I set that column output length as 50.
Now when I'm running the package it failing again with different error this time that says:
The value could not be converted because of a potential loss of data.
So I got stuck here. No option. I cant change source file and destination column datatype.
Please guide me!! Its weird
__________________________________________
---------------------------------------------------
Save our mother Earth. Go Green !!!
March 10, 2014 at 9:47 am
You need to see what's going on here. Add a data flow for On Error output to a harmless transformation (I always used Copy Column) and put data viewers on both paths. That will show you what's being generated by your expression.
Hopefully that will give you some insight into what part is being cranky.
One thought - is your NonCashAjmt field ever NULL? That could be causing problems with your substrings.
March 10, 2014 at 9:54 am
You could also set the column to ignore truncation errors if you are certain your data won't overflow the allowed space.
Truncation errors in SSIS are annoying sometimes.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply