Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SSIS package works properly in VS but hangs after deploy (some records inserted then crashed)

Status
Not open for further replies.

mahdi-the-man

Programmer
Apr 2, 2017
1
IR
I have 5 sql servers as a source and one Oracle database as a Data wareHouse (destination). 3 of them (sources) works properly in VS and also after deployed. but 2 of them works properly in VS but failed in IntegrationServices after some records inserted!.

SSIS server config:

[ul]
[li]4GB ram[/li]
[li]64 bit windows server 2008 r2[/li]
[li]cpu xeon 2.30 GHz (2 processors)[/li]
[li]there are 778280 records in the source.[/li]
[li]buffer size in SSIS (and other configs) set to default values.[/li]

[/ul]

the package runs properly in the visual studio but after deploying to the IntegrationServices and executing ,it crashes after inserting about 333900 (some time 333500, and sometimes 334100, ...) records , with these error messages:

[PACKAGE NAME]:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error Code: 0x90050E2F, An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E2F Description: "Value violated the integrity constraints for a column or table".

[PACKAGE NAME]:Error: SSIS Error Code DTS_E_INCLUDTRANSFORMFAILUREONERROR. The "OLE DB Destination.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row diposition on "OLE DB Destination.Inputs[OLE DB 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.

as u can see, it tells "Value violated the integrity constraints ......" but:

there are no constraints in the destination (no PK, no Unique index, nothing)
the package runs properly in VS!!! (and all of 778280 records inserted into destination!!) so there is no integrity constraint violation happened (or memory limitation)!
I tested for many input ranges to see if some records in the source has errors. ie: i sort the input descending, ascending, sort by different column,... and in all of them, only about 334100 (or something like that) records inserted in the destination and then crashed.
I double checked 32/64 bit package properties to sure about that.

so the question is: whats happened in the IntegrationServices? Is there any limitation (memory-related,...) in my scenario?

thanks in advance
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top