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
chanterocAsked:
Who is Participating?
 
NetminderCommented:
Closed, 125 points refunded.
Netminder
Site Admin
0
 
apirniaCommented:
add single quotes around it befor import
0
 
chanterocAuthor Commented:
I've tried that in the ActiveX:

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

Still getting the errors.

Thanks!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
apirniaCommented:
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.
0
 
chanterocAuthor Commented:
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...
0
 
arbertCommented:
What are the exact errors you receive?  A + or a single quote shouldn't cause problems on a simple column transform....
0
 
chanterocAuthor Commented:
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

****************************************************************************************************
0
 
apirniaCommented:
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.
0
 
arbertCommented:
When you watch the job run by using SQL Profiler, do you see any warnings (like data truncation warnings)?
0
 
chanterocAuthor Commented:
I'll try watching the job in SQL Profiler; I can't forward any of the data, though, as it's proprietary.
0
 
arbertCommented:
"I can't forward any of the data, though, as it's proprietary."


That makes for an easy problem to solve....
0
 
chanterocAuthor Commented:
Unfortunately - HIPAA precludes...
0
 
arbertCommented:
There are also policies within HIPAA for releasing data by deidentifying information....
0
 
chanterocAuthor Commented:
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!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.