INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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:

Quote:

[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".

Quote:

[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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close