Hi All: I'm trying to copy a table between two sql2008R2 sp2 servers. The table has just 239 rows. I've tried SQLNativeClient and OLEDBforSQLServer with the same result. The table has a mixture of varchar fields, datetimes and ints, but none of the varchars are more than 1024 in length, and the total row size is small. I keep getting 0xC0209029 errors copying the table. SSIS drops and recreates the table, reads the rows, but seems to error out before copying/inserting. Is there any way to isolate which column is causing the problem? Or is this not a data problem at all? My error message is below. Messages Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "Unspecified error". (SQL Server Import and Export Wizard) Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "Destination Input" (110)" failed because error code 0xC020907B occurred, and the error row disposition on "input "Destination Input" (110)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard) Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - ems_product" (97) failed with error code 0xC0209029 while processing input "Destination Input" (110). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

The key message in the error is DTS_E_INDUCEDTRANSFORMFAILUREONERROR. This means that a column that's being inserted is not the same type as the destination column AND the implicit conversion is failing. This is usually caused when attempting to import string data into numeric or date typed columns. Do the source table and destination table have the same schema? Talk to me now on

1) Yes, source and destination schema should be similar during the import or export , else it fails. 2) If there are data truuncation symptoms(source columns size is more than the destination column size) , it should give a warning. The package or wizard will be a success in this case if the data is ok, but fails if the data size is more than the destination size. Happy to help! Thanks. Regards and good Wishes, Deepak.

I thought the source and destination were identical, but it's possible the source may have been modified. I dropped the table in the destination, then recreated it from the original using "Generate Scripts", and then I was able to import. Thanks, I was remembering from the old SQL2000 days when you could drop/recreate tables during import...but this worked fine.