CODEWITHSUNDEEP
sql-serverssis
Blair Varley
Blair Varley

Reputation: 3

Identity Inserts using SSIS Import Export Wizard

I have a text file that I am using 2012 SSIS to load into a table designed specifically for the data in SQL Server 2012. I have a column identified as a primary key with identity specification turned-on (no columns in the source text file are mapped to this column). When I am using SQL Server Import and Export Wizard to create the package with "Enabled identity" inserts check box checked in the Column Mapping dialog, I get the below errors and the data does not load. If I delete the column with the primary key and the identity specification and execute the SSIS package again the data loads properly without errors. Why I am not able to load the data with a primary key and identity turned on? Thanks.

[Destination - shoppingExtractPrecise_WithIdentity3 [104]] Error: SSIS Error Code DTS_E_OLEDBERROR. An LE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Cannot insert the value NULL into column 'shoppingExtract_ID', table 'shopping_Pharmacy.dbo.shoppingExtractPrecise_WithIdentity3'; column does not allow nulls. INSERT fails.".

[Destination - shoppingExtractPrecise_WithIdentity3 [104]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Destination - shoppingExtractPrecise_WithIdentity3.Inputs[Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "Destination - shoppingExtractPrecise_WithIdentity3.Inputs[Destination Input]" 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.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Destination - shoppingExtractPrecise_WithIdentity3" (104) failed with error code 0xC0209029 while processing input "Destination Input" (117). 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.

[Source - SOL 08112016_txt [173]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on Source - SOL 08112016_txt returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Upvotes: 0

Views: 314

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31785

When the "Enable Identity Inserts" checkbox is CHECKED, that means that SQL expects you to import values to the Identity column. If you do not import values to that column, or import any rows that have a NULL value for that column, you will get the error you got.

When it is NOT CHECKED, then you can import values to all the other columns and SQL Server will auto-populate the column for you.

So from what you describe, it sounds like you want to leave that box NOT CHECKED.

Upvotes: 1

Related Questions