April 2, 2008 at 8:40 am
Hi,
The problem was long enough to take my whole day. To make story short-- i have flat file with uneven number of columns in each row. so i have a script component to handle that part.
But now the problem is I added a data conversation in my dataflow which is converting string [DT_str] 50 into database timestamp, which is my sql table destination datatype. Following is the error i get when i run the task--
ERROR:--
[Data Conversion [2628]] Error: Data conversion failed while converting column "Col3" (923) to column "Copy of Col3" (2651). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
[Data Conversion [2628]] Error: Data conversion failed while converting column "Col3" (923) to column "Copy of Col3" (2651). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
the follow of my datatask is i have flat file and i am inporting all columns(i.e. my row of different varying lengths) into single column.
then i have a script components to handle the uneven length of columns based on vertical line.
then i have a data conversation task that converts col3 string into timestamp
and finally i load data into sql destination
-----When i change the option on error , ignore the failure, it works , all the data gets loaded.
however. my team wants me to find alternatives.
CAn someone help me other way i can achive the same results or am i doing something wrong on my conversation?
thanks, kumar
April 4, 2008 at 8:12 pm
Change your on error to redirect row and output the data to a file. Then you can examine the data that is being rejected by the conversion and determine why the conversion is failing. Are you sure ALL the data is loaded when you have on error set to ignore? You are probably getting a Null or truncated value in the error row.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 5, 2008 at 9:01 am
Thanks Jack, I know the problem , ALl the rows where 3rd column which is database timestamp is the problem. the rows where this column is empty is given me the problem. there are 82 rows , out of which 22 rows in flat file have this 3rd row, the rest 60 columns are empty. so what SQL serevr is trying to do is convert this empty string ( I guess so) . when i redirected the rows these empty columns are the ones thats get redirected. when u see in the table all the rows gets loaded when i try ignore on failure.
Another thing here is the column is not empty actually. when i do
select * from table ......
where column 3 is NULL , gives me zero rows and when i do where column3 is not null gives me all rows
Things are different in SSIS, when it comes to NULL.
its converting null values from flat file into empty string.
I can do rediret row, conditional spilt, or derievd column. But my head wants me to load data in the oredr of the flat file........
Thanks....
April 6, 2008 at 7:09 am
It was not clear to me that you knew what data was causing the problem. In this case ignore error works fine as you have no data there anyway. MY concern was that data was actually being lost. The other thing to remember is that a flat file does not have a NULL value it would be an empty string which is how SSIS is handling it. I think you could convert the empty string to NULL in another data conversion task and then your current one would not fail, it would pass through the Null.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 7, 2008 at 8:03 am
Change the empty string to some unused date (eg 1/1/70) then convert to null after import.
July 15, 2009 at 11:34 pm
hey,
Use Drived Column Component before Data conversion and use this expression
TRIM(birth_date) == "" ? (DT_DBTIMESTAMP)NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(DT_STR,10,1252)(SUBSTRING(TRIM(birth_date),5,4) + "/" + SUBSTRING(TRIM(birth_date),1,2) + "/" + SUBSTRING(TRIM(birth_date),3,2))
it will replace the empty string by nulll.
it will work..
December 4, 2012 at 1:24 am
Hi, I had a column effdt(effective date) where sum values were blanks n sum had the date values...I needed to populate them to the staging tables....
The solution to my problem was to
Just add a derived column der_effdt add as a new column ...REPLACE(Effdt," ",NULL(DT_WSTR,50)) and then use data conversion task to convert the der_effdt to date(dt_date) data type...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply