Randome error in SSIS (2008) OLE DB command

Question

0.00/5 (No votes)

See more:

Hello everyone,

I come to you for help to understand some odd behavior in a 2008 SSIS package.

In a classical Insert New -> Update Existing scenario (DB Source to DB Destination) both SQL 2008 R2, we are getting a few bad records with the following error number:
-1071607703 {DTS_E_COMMANDDESTINATIONADAPTERSTATIC_CANTCONVERTVALUE}
the additional information points at a varchar(60) column, and our Select statement is in fact Casting the source value to varchar(60).

In face of such error, we created a twin table with a larger varchar(field) to store "bad" values so that we could analyze them. Well, we found nothing; each record had 60 or less characters in length and we were even able to execute an UPDATE statement between the originally intended target table and the "bad" one without a single issue, and without having to cast or convert a single value. This behavior repeated time and time again. Does this make any sense?

Prior deploying the package to production, we did test it for all three scenarios:
a) all insert
b) some insert, some update
c) all update

Once deployed, it ran without issues for about 20hrs (every 10') before showing said behavior.

Any suggestions?

What I have tried:

We copied the source DB from production into a development server (also 2008 R2), and ran the SSIS in debug mode from SSBIDS without a single error.

Thank you all for your time and thoughts.

Posted 18-Oct-17 9:25am

alexvw

Updated 19-Oct-17 2:39am

Add a Solution

Comments

Richard MacCutchan 19-Oct-17 5:06am

You did not mention what value it is complaining about.

alexvw 19-Oct-17 6:17am

Good morning Richard, it is not a
specific value; it's come up with various records (not necessarily the same ones). Anyway, these are simple strings. The source is:

Select Cast(Trim(field01) as varchar(60)) as InputValue
From dbo.sourcetable

The destination table field is declared as varchar(60).

All values get inserted without issues, but the described error ramdomly appears during updates and only in the production environment. By the way, it even comes up when trying to update the inserted record with it own values.

Oddly, that same data and SSIS do not cause incidents while operating in development environment.

Sorry I can't be more specific; oh! I can say these strings do not have troubling characters such as: ', ", --, ; , etc.)

Anyhow, thank you for looking into it. I'll make sure to share any findings on my side.

Richard MacCutchan 19-Oct-17 6:23am

Yes, but what is in field01, and why do you need a Cast?

alexvw 19-Oct-17 6:34am

The source is a char (100) field, which contains various length values not larger than 50 characters.

It was decided to dump them into a varchar column (60 in length) thus leaving room for unforeseen larger values. Hence, the Cast.

1 solution

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)

alexvw · Accepted Answer · 2017-10-19T02:39:00

Dear all,

Given the situation, I have decided to keep the necessary data (and SSIS) appart for further study.

Therefore, I made a "new copy" of the SSIS and deleted the entire transformation task in order to rebuild it from scratch. So far, all test runs of the rebuilt SSIS have been successful.

Thank you all for the given thought. I will publish any findings on this matter.

Cheers!