Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1748
  • Last Modified:

DTS Insert error, status 6: Data overflow.

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
ITAMike
Asked:
ITAMike
1 Solution
 
arbertCommented:
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
 
ITAMikeAuthor Commented:
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
 
mcp111Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
arbertCommented:
" 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
 
ITAMikeAuthor Commented:
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
 
arbertCommented:
Yep, pretty much....Add a source, target, and draw a transformation between the two.....
0
 
ITAMikeAuthor Commented:
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
 
xenon_jeCommented:
--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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now