Solved

DTS Insert error, status 6: Data overflow.

Posted on 2004-09-27
8
1,715 Views
Last Modified: 2008-01-09
Trying to copy some data from an external source to a new table in SQL.  I receive the following error:

Error at Destination for Row number 1. Errors encoutered so far in this task: 1.
  Insert error, column 55 ('rdate3', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
  Insert error, column 54 ('rdate2', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
  Insert error, column 53 ('rdate1', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
  Invalid character value for cast specification.


I have no use for information in cols 55, 54, 53.  I'd be happy to either not import those columns or to fix the mappings and transforms in a way that will allow me to run the import without error.  

Not sure if it's useful but the source table is Visual FoxPro, 150MB, 200 columns, tens of thousands of records.

Thanks,
ML
0
Comment
Question by:ITAMike
8 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 250 total points
ID: 12165033
So why don't you open the package in designer and get rid of column 53-55 from the transform.

If you used the import wizard to setup the original package, it probably grouped the transform into one column copy transform.  So, you'll probably have to delete the columns you don't want from the input side of the transform and then remap the columns.
0
 

Author Comment

by:ITAMike
ID: 12165311
I don't want to assume anything as I'm rather inexperienced...

The DSN for the import was created using the default VFP ODBC driver.  Thus the entire database structure is available through the DTS wizard.  When I get to the Select Souce Tables and View, I see a list of all tables in the database.  I click on the checkbox for the one table I'm interested in, then click the Transform [...] button.  This brings up the Column Mappings and Transformations tabs.  Are you talking about edits to either of these areas??

I am unclear as to where to specify which columns to import and which to ignore.

ML
0
 
LVL 6

Expert Comment

by:mcp111
ID: 12165339
In that dialog there is a Transformation tab which allows you to specify the transformations between source and destination. IN that tab for columns 53-55, choose ignore from the dropdown for the field menu in the Destination column.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12165424
" IN that tab for columns 53-55, choose ignore from the dropdown for the field menu in the Destination column."

Exactly, if you're creating a new transform select ignore.  If it's an existing package, go into Enterprise manager, data transformation services, local packages and edit the existing package.  If you double click on the transformation "line" between the source and destination, you can delete the columns that aren't needed....
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:ITAMike
ID: 12165506
Gents,

It appears I need to setup my own DTS package, which I have never done.  I was simply using the wizard.  The wizard has no "ignore" to select.  So let me ask you...creating a package from scratch...Do I assume it's like building a flowchart?  Place the source data in as an icon, place the destination table as an icon, create a type of transfer that connects the two, etc, etc.??
0
 
LVL 34

Expert Comment

by:arbert
ID: 12165522
Yep, pretty much....Add a source, target, and draw a transformation between the two.....
0
 

Author Comment

by:ITAMike
ID: 12165541
OK I'm all set to test this.  One more thing.  Is there an easy way to copy a table structure but not the data from one SQL database to another.  I want to prove I can do it with a test table before attempting on a live table.
Thanks!
0
 
LVL 9

Expert Comment

by:xenon_je
ID: 12165662
--like this you seee only column name
select top 0 * from tablename

--like this you copy the structure to a newtable
select top 0 * into newtable from tablename

--like this you copy the structure to a newtable from a table that is in a linked table, lie in your case a foxpro table
select top 0 * into newtable from (select * from linkedservername.tablename)


good luck,
  xenon
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now