asked on

Strange SSIS error when loading data from excel

All,

I have a very simple package in SSIS that I am running. It takes 37 columns worth of data and loads them into a single table. I do have to do some data conversions on the data types for some of the columns going into SQL but they are all either strings or integers. The error below references a column called SlideNumber which gets converted into Copy of SlideNumber which then gets loaded into the SQL destination table. So I am not really sure why I am seeing this error. Especially since the data actually loads into the table prior to error causing this task to crash the package and prevent a couple of other column updates. Any ideas/suggestions would be greatly appreciated.

Thanks.

Ken

ERROR MESSAGE in SSIS output tab:

SSIS package "CSDataLoad.dtsx" starting.
Information: 0x4004300A at Load Case Study Data, SSIS.Pipeline: Validation phase is beginning.
Information: 0x4004300A at Load Case Study Data, SSIS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Load Case Study Data, SSIS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Load Case Study Data, SSIS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Load Case Study Data, SSIS.Pipeline: Execute phase is beginning.
Error: 0xC0202009 at Load Case Study Data, OLE DB Destination [609]: 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".
Error: 0xC020901C at Load Case Study Data, OLE DB Destination [609]: There was an error with input column "Copy of Slide Number" (699) on input "OLE DB Destination Input" (622). The column status returned was: "The value violated the integrity constraints for the column.".
Error: 0xC0209029 at Load Case Study Data, OLE DB Destination [609]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (622)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (622)" 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.
Error: 0xC0047022 at Load Case Study Data, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (609) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (622). 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.
Information: 0x40043008 at Load Case Study Data, SSIS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at Load Case Study Data, OLE DB Destination [609]: The final commit for the data insertion in "component "OLE DB Destination" (609)" has started.
Information: 0x402090E0 at Load Case Study Data, OLE DB Destination [609]: The final commit for the data insertion in "component "OLE DB Destination" (609)" has ended.
Information: 0x4004300B at Load Case Study Data, SSIS.Pipeline: "component "OLE DB Destination" (609)" wrote 80 rows.
Information: 0x40043009 at Load Case Study Data, SSIS.Pipeline: Cleanup phase is beginning.
Task failed: Load Case Study Data
SSIS package "CSDataLoad.dtsx" finished: Success.

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ktQueBIT

ASKER

The column is the PK for the table and is a NON_NULL column. However, all the records in the excel source file have a defined value for this column. The strange thing is this SSIS package worked perfectly prior to migrating to a new SQL server instance. So at this point I am not sure why this would be happening.