Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DATA CONVERSATIONS TASKS FAILS IN SSIS Expand / Collapse
Author
Message
Posted Wednesday, April 2, 2008 8:40 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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
Posted Friday, April 4, 2008 8:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:57 AM
Points: 10,387, Visits: 13,454
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
Posted Saturday, April 5, 2008 9:01 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, August 13, 2013 9:44 AM
Points: 627, Visits: 509
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
Posted Sunday, April 6, 2008 7:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:57 AM
Points: 10,387, Visits: 13,454
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

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 #480478
Posted Monday, April 7, 2008 8:03 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 22, 2014 9:25 AM
Points: 820, Visits: 2,137
Change the empty string to some unused date (eg 1/1/70) then convert to null after import.


Post #480836
Posted Wednesday, July 15, 2009 11:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, December 4, 2012 1:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 28, 2013 8:57 AM
Points: 1, Visits: 16
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 »

Add to briefcase

Permissions Expand / Collapse