DATA CONVERSATIONS TASKS FAILS IN SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » DATA CONVERSATIONS TASKS FAILS IN SSIS

DATA CONVERSATIONS TASKS FAILS IN SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

SSIS\SSRS\SSAS

Posted Wednesday, April 02, 2008 8:40 AM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

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

Post #478573

Jack Corbett

Posted Friday, April 04, 2008 8:12 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 10,613, Visits: 11,952

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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #480342

SSIS\SSRS\SSAS

Posted Saturday, April 05, 2008 9:01 AM

SSChasing Mays

Group: General Forum Members
Last Login: Saturday, October 06, 2012 4:41 PM
Points: 626, Visits: 507

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....

Post #480396

Jack Corbett

Posted Sunday, April 06, 2008 7:09 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 10,613, Visits: 11,952

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.

Post #480478

RonKyle

Posted Monday, April 07, 2008 8:03 AM

Say Hey Kid

Group: General Forum Members
Last Login: Today @ 2:03 PM
Points: 678, Visits: 1,543

Change the empty string to some unused date (eg 1/1/70) then convert to null after import.

Post #480836

chatwithkrishan

Posted Wednesday, July 15, 2009 11:34 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 11, 2013 6:50 AM
Points: 38, Visits: 292

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..

Post #753974

baani.khorana

Posted Tuesday, December 04, 2012 1:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 4:54 PM
Points: 1, Visits: 14

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...

Post #1392278

« Prev Topic | Next Topic »

Permissions