Solved

DTS Insert error, status 6: Data overflow.

Posted on 2004-09-27
8
1,737 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

734 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