Solved

DTS Insert error, status 6: Data overflow.

Posted on 2004-09-27
8
1,732 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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
 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Options for Linking SQL tables to Access 2013 9 42
Convert time stamp to date 2 57
calculate running total 8 16
SQL Help 4 16
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 …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

726 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