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

Question

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

mahdi-the-man

SSChasing Mays

Points: 645
More actions
April 2, 2017 at 6:59 am

#320066
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:
- 4GB ram
- 64 bit windows server 2008 r2
- cpu xeon 2.30 GHz (2 processors)
- there are 778280 records in the source.
- buffer size in SSIS (and other configs) set to default values.
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:
1. there are no constraints in the destination (no PK, no Unique index, nothing)
2. 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)!
3. 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.
4. 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I know next to nothing about Oracle, but I'd guess that "integrity constraints" might include data types and check constraints as well as primary and foreign keys. Have you tried using breakpoints, data watchers and so on in the SSIS package to see what's happening at the time of the error?

John

Thom A SSC Guru Points: 99126 More actions · Answer 2

You could try setting your bad rows to go to a different destination for now, for simplicity you could do it to a csv/text file. Then have a look at the records that have come out, and see if anything seems to click.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

mahdi-the-man SSChasing Mays Points: 645 More actions · Answer 3

John Mitchell-245523 - Monday, April 3, 2017 5:22 AM
I know next to nothing about Oracle, but I'd guess that "integrity constraints" might include data types and check constraints as well as primary and foreign keys. Have you tried using breakpoints, data watchers and so on in the SSIS package to see what's happening at the time of the error?
John

yeah the message thats clear (integrity constraints violation) but: I changed the query to this:
SELECT 1 as 'PayPrice' FROM Cash

as u can see, there's only one column with 1 value. and there's about 800000 records in the source. but after inserting about 563810 records, the same error happened and the package execution failed.

and (i'm surprised) sometimes this message shown (and the package execution status changed to "Unexpected termination" :

and here the error message in event viewer:

Faulting application name: ISServerExec.exe, version: 12.0.2000.8, time stamp: 0x5306c81f
Faulting module name: KERNELBASE.dll, version: 6.1.7601.18015, time stamp: 0x50b83c8a
Exception code: 0xc0000005
Fault offset: 0x0000c41f
Faulting process id: 0x1c2c
Faulting application start time: 0x01d2acafda0c16e8
Faulting application path: C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\ISServerExec.exe
Faulting module path: C:\Windows\syswow64\KERNELBASE.dll
Report Id: 9964d77e-18a4-11e7-995c-005056af0830

mahdi-the-man SSChasing Mays Points: 645 More actions · Answer 4

Thom A - Monday, April 3, 2017 6:09 AM
You could try setting your bad rows to go to a different destination for now, for simplicity you could do it to a csv/text file. Then have a look at the records that have come out, and see if anything seems to click.

hi Thom A
could u plz see my prev post replied to John Mitchell-245523?
thnx

mahdi-the-man SSChasing Mays Points: 645 More actions · Answer 5

for everyone who interested, i changed the adapter to Oracle Attunity, and everything's going right! thanx guys