Solved

DTS Insert error, status 6: Data overflow.

Posted on 2004-09-27
8
1,729 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
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.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
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 extract information from SQL Server on Database, Connection and Server properties

837 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