Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DATA CONVERSATIONS TASKS FAILS IN SSIS


DATA CONVERSATIONS TASKS FAILS IN SSIS

Author
Message
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
Say Hey Kid
Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)

Group: General Forum Members
Points: 665 Visits: 513
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
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11034 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
SSIS\SSRS\SSAS
SSIS\SSRS\SSAS
Say Hey Kid
Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)Say Hey Kid (665 reputation)

Group: General Forum Members
Points: 665 Visits: 513
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....
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11034 Visits: 14858
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
RonKyle
RonKyle
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1282 Visits: 3343
Change the empty string to some unused date (eg 1/1/70) then convert to null after import.



chatwithkrishan
chatwithkrishan
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
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..
baani.khorana
baani.khorana
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search