?
Solved

DTS Import Fails - Punctuation in Varchar Column

Posted on 2005-04-14
16
Medium Priority
?
380 Views
Last Modified: 2013-11-30
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
0
Comment
Question by:chanteroc
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 9

Expert Comment

by:apirnia
ID: 13784223
add single quotes around it befor import
0
 

Author Comment

by:chanteroc
ID: 13784298
I've tried that in the ActiveX:

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

Still getting the errors.

Thanks!
0
 
LVL 9

Expert Comment

by:apirnia
ID: 13784401
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:chanteroc
ID: 13784564
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
 
LVL 34

Expert Comment

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

Author Comment

by:chanteroc
ID: 13786404
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
 
LVL 9

Expert Comment

by:apirnia
ID: 13786466
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
 
LVL 34

Expert Comment

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

Author Comment

by:chanteroc
ID: 13786667
I'll try watching the job in SQL Profiler; I can't forward any of the data, though, as it's proprietary.
0
 
LVL 34

Expert Comment

by:arbert
ID: 13786771
"I can't forward any of the data, though, as it's proprietary."


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

Author Comment

by:chanteroc
ID: 13789767
Unfortunately - HIPAA precludes...
0
 
LVL 34

Expert Comment

by:arbert
ID: 13798873
There are also policies within HIPAA for releasing data by deidentifying information....
0
 

Author Comment

by:chanteroc
ID: 13807327
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
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 13847854
Closed, 125 points refunded.
Netminder
Site Admin
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question