?
Solved

Strange SSIS error when loading data from excel

Posted on 2012-08-16
2
Medium Priority
?
801 Views
Last Modified: 2013-06-19
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.
0
Comment
Question by:ktQueBIT
2 Comments
 
LVL 40

Accepted Solution

by:
lcohan earned 1500 total points
ID: 38301344
"The value violated the integrity constraints for the column.".

What constraint you have on that "Copy of Slide Number"  column?
Maybe is just NOT NULL and you have NULLS coming into it from the import?
0
 

Author Comment

by:ktQueBIT
ID: 38312046
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.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 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