asked on

DTS Import Fails - Punctuation in Varchar Column

Howdy all,

I'm stumped by what seems to me to be a non-issue, but which has been giving me fits today. I'm trying to import a table from an ODBC data source (in this case, a MUMPS database). One of the columns, a text column, contains the value "40+" (no quotes in the data) for multiple rows. I am trying to import this column into a varchar(255) column, but the presence of the "+" sign throws multiple errors and the DTS package fails. I've tried using an ActiveX transform as well as a Copy Column, but neither works. Can someone suggest an alternative that will allow me to successfully pull the data?

Thanks,

John

apirnia

add single quotes around it befor import

chanteroc

ASKER

I've tried that in the ActiveX:

DTSDestination("Class") = "'" & DTSSource("Class") & "'"

Still getting the errors.

Thanks!

apirnia

Thats wiered....Maybe Cast/Convert would work. I would try it with Cast first. Since you are inseritng to a Varchar (255) just use the same thing in your cast condition as well for all fields.

Lets see if this workes.....if it did we can add a Case statement to do it the fields that starts with a Number.

chanteroc

ASKER

Actually, I see the error whether or not I try to transform the column; as long as I'm pulling it in, even when it's not referenced other than in the SELECT stratement, the package bombs.

Weird...

arbert

What are the exact errors you receive? A + or a single quote shouldn't cause problems on a simple column transform....

chanteroc

ASKER

That's the interesting thing; the error log is non-specific. If I remove the column of interest, though, the package executes just fine.

The errors I'm getting are:

Execution Started: 4/14/2005 5:50:00 PM
Error at Source for Row number 2245. Errors encountered so far in this task: 1.
Error at Source for Row number 7847. Errors encountered so far in this task: 2.
Error at Source for Row number 9814. Errors encountered so far in this task: 3.
Error at Source for Row number 12730. Errors encountered so far in this task: 4.
Error at Source for Row number 12901. Errors encountered so far in this task: 5.
Error at Source for Row number 13944. Errors encountered so far in this task: 6.
Error at Source for Row number 16191. Errors encountered so far in this task: 7.
Error at Source for Row number 16240. Errors encountered so far in this task: 8.
Error at Source for Row number 18336. Errors encountered so far in this task: 9.
Error at Source for Row number 19535. Errors encountered so far in this task: 10.
Error at Source for Row number 21966. Errors encountered so far in this task: 11.
Execution Completed: 4/14/2005 5:50:10 PM

****************************************************************************************************

apirnia

Can you also paste the information from couple of these rows. Not just the field you think erroring, all the fields you are trying to map.

arbert

When you watch the job run by using SQL Profiler, do you see any warnings (like data truncation warnings)?

chanteroc

ASKER

I'll try watching the job in SQL Profiler; I can't forward any of the data, though, as it's proprietary.

arbert

"I can't forward any of the data, though, as it's proprietary."

That makes for an easy problem to solve....

chanteroc

ASKER

Unfortunately - HIPAA precludes...

arbert

There are also policies within HIPAA for releasing data by deidentifying information....

chanteroc

ASKER

Thanks - HIPAA aside (I'll be the first to admit I only know what I've been told on this one :-)) - the issue turns out to be on the MUMPS side and will have to be corrected there. Users are placing non-numeric data into a numeric column; this is what's causing the error on export. The SQL Server side is fine.

Thanks for all your help!

ASKER CERTIFIED SOLUTION

Netminder

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial