The value could not be converted because of a potential loss of data

Question

0.00/5 (No votes)

See more:

Hi,

I am facing this problem since very long.

I am fetching the data from source (i.e DB2) into SQL server 2008.

while doing that I have got this error.

VB

Error #1
[OLE DB Source [1]] Error: There was an error with output column "ZONE" (185) on output "OLE DB Source Output" (11). The column status returned was: "The value could not be converted because of a potential loss of data.".

Error #2
[OLE DB Source [1]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "ZONE" (185)" failed because error code 0xC0209072 occurred, and the error row disposition on "output column "ZONE" (185)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.

Error #3
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC0209029.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

Pls Help !!!!!!!!!!!

Posted 18-Jun-15 0:30am

Rohit Lal

Add a Solution

Comments

Andy Lanng 18-Jun-15 6:48am

this error is due to a datatype conversion that would be a 'lossy conversion'

Show us your code. Show us your query. Show us your data.

Rohit Lal 18-Jun-15 7:22am

Query
===============FIRED ON SOURCE TABLE
SELECT
CIRCLENAME,NODE,siteid,zone,cluster,ALARMCODE, evnttime,CLEAR_TIME,FIRSTOCCURRENCE,LASTOCCURRENCE,LOGTICKET
FROM DB2CDC. REPORTER_STATUS WHERE
(EVNTTIME BETWEEN '2015-06-15-00.00.00.000000' AND '2015-06-15-23.59.59.000000')
WITH UR
==========DATA TYPE IN DB2=====
CIRCLENAME (VARCHAR(15))
NODE (VARCHAR(64))
SITEID (VARCHAR(45))
ZONE (VARCHAR(20))
CLUSTER (VARCHAR(25))
ALARMCODE (VARCHAR(12))

Truncation only happened in above column. I have specified the same column and data type in sql table.

Andy Lanng 18-Jun-15 8:21am

Ok - better. For sake of ease I have posted my reply as a solution.

CHill60 18-Jun-15 6:54am

In addition to Andy's comment above, you could have typed "Please help!" instead of "Pls Help !!!!!!!!!!!", saved yourself 8 keystrokes and have avoided looking childish. Avoid text-speak if you don't want to appear unprofessional.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-06-18T01:10:00

Solution 1

Start by checking the column definitions in each table: the chances are that either:
1) A DB2 text column is longer than the SQL table equivalent, so SQL is complaining that it would have to truncate data.
Or
2) The DB2 column contains binary data and you are trying to insert it to a text based SQL column.

Sorry, but we can't be specific without specific information.

Posted 18-Jun-15 1:10am

OriginalGriff

Comments

Rohit Lal 18-Jun-15 7:22am

Query
===============FIRED ON SOURCE TABLE
SELECT
CIRCLENAME,NODE,siteid,zone,cluster,ALARMCODE, evnttime,CLEAR_TIME,FIRSTOCCURRENCE,LASTOCCURRENCE,LOGTICKET
FROM DB2CDC. REPORTER_STATUS WHERE
(EVNTTIME BETWEEN '2015-06-15-00.00.00.000000' AND '2015-06-15-23.59.59.000000')
WITH UR
==========DATA TYPE IN DB2=====
CIRCLENAME (VARCHAR(15))
NODE (VARCHAR(64))
SITEID (VARCHAR(45))
ZONE (VARCHAR(20))
CLUSTER (VARCHAR(25))
ALARMCODE (VARCHAR(12))

Truncation only happened in above column. I have specified the same column and data type in sql table.

Andy Lanng · Answer 2 · 2015-06-18T02:21:00

Solution 2

First - double check the column types. Run this on both tables
sp_help 'table_name'. You will see some columns specifying length, precision and scale. These should all match.

Next: maybe something else is happening that you can't see. Try this:
SELECT * FROM sys.triggers WHERE OBJECT_ID = OBJECT_ID('table_name'). If there is a trigger then that maybe trying to something else with your data.

Reply if both of those check out

EDIT: OP reports columns and triggers check out fine.

This may be a specific framework issue that use some 3rd party ODBC drivers. These drivers can mess up the datatypes in translation.

Take a look at this discussion on the driver issues. There is a hotfix and it may be the fix you need:

https://support.microsoft.com/en-us/kb/969845[^]

Posted 18-Jun-15 2:21am

Andy Lanng

Updated 18-Jun-15 3:49am

v2

Comments

Rohit Lal 18-Jun-15 9:28am

DB2 SP HELP

NAME TBNAME COLTYPE NULLS CODEPAGE DBCSCODEPG LENGTH
ALARMCODE REPORTER_STATUS VARCHAR Y 1208 1200 12
CIRCLENAME REPORTER_STATUS VARCHAR Y 1208 1200 15
CLUSTER REPORTER_STATUS VARCHAR Y 1208 1200 25
NODE REPORTER_STATUS VARCHAR Y 1208 1200 64
SITEID REPORTER_STATUS VARCHAR Y 1208 1200 45

SQL SP HELP

Column_name Type Computed Length Prec Scale
CIRCLENAME varchar no 15

NODE varchar no 64

SITEID varchar no 45

ZONE varchar no 20

CLUSTER varchar no 25

ALARMCODE varchar no 12

No triggers found in both side (source and destination table).
I am new to this.

Rohit Lal 18-Jun-15 9:30am

Still showing same error.

Andy Lanng 18-Jun-15 9:50am

Ok - I have found a possible issue which there is a hotfix for. Please read the article carefully to ensure that it applies to your situation (I'm pretty sure it does)
Let me know the outcome ^_^

Rohit Lal 18-Jun-15 10:25am

Hotfix applied but still facing this issue

Andy Lanng 18-Jun-15 11:05am

Has this ever worked? If so, did something change?

Rohit Lal 19-Jun-15 3:29am

Hi Andy,
I am getting two error while running HOT fix

Error-1
The version of SQL Server instance Shared Component does not match the version expected by the SQL Server update. The installed SQL Server product version is 10.0.1600.22, and the expected SQL Server version is 10.1.2531.0.

Error-2
[Error Message]
There are no SQL Server instances or shared features that can be updated on this computer.

Can you help on the same.

Andy Lanng 19-Jun-15 4:14am

You need to update to SP1 at least (maybe further)
SQL 2008 SP1
SQL 2008 SP1 Cumulative Updates

So basically: Either the tools you are using are out-of-date (did someone upgrade DB2 recently?) or the field types are not compatible. The first is most likely but takes a while to set up. The second is (now) less likely but shouldn't take long to find.

Can you check the Collation compatibility?
Is MS SQL it's just SELECT * FROM sys.columns WHERE OBJECT_ID = OBJECT_ID('table_name'). Text fields will have a collation. I 100% sure how to find the collation on DB2 but I think you can use something like:
Select COLLATION_KEY(column_name) from table_name
If there is a mismatch then the fix is 2 seconds!

Let me know :)
(PS: Hit reply to this comment so I get notified ^_^ )

Rohit Lal 19-Jun-15 9:25am

Not able to find COLLATION KEY in DB2

Andy Lanng 19-Jun-15 10:00am

i don't use DB2 so I guess we're stuck there. I searched for how to find it and that was what I found. Maybe you would have better luck.

There is always option one but if the collation is the issue then it wouldn't help.

You can change the collation of either databases fields in a select query but you have to know what to collate them to

Rohit Lal 24-Jun-15 4:02am

Any other solution ?

Andy Lanng 24-Jun-15 4:07am

The only thing left is collation (language set).

if you are inserting into DB2 then you need to know what the collation set of DB2 is. This has to be your next step :S

If you are inserting into MSSQL then we know what collation to use.