question

apple avatar image
apple asked

weird issue while executing package

I have a package which drops tables the recreates them. The package fails while creating tables it does not fail always at at the same table. What I observed is that if I execute the container which drops the tables twice the package does not fail. Database is oracle. Any ideas what could be the issue? Thanks a lot
ssisoracle
5 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
added oracle tag
0 Likes 0 ·
ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Are there any cross-table dependencies? Foreign Keys, that sort of thing? If so, is your script / package dropping & creating in the right order?
0 Likes 0 ·
Pavel Pawlowski avatar image Pavel Pawlowski commented ·
Also error message, which is produced when the package fails would be helpful.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
@apple - is this solved? you've asked other questions since this one, but not added any of the requested information to this one. "Help us to help you" as the saying goes.
0 Likes 0 ·
apple avatar image apple commented ·
following error: "ORA-00955: name is already used by an existing object ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE this is the error associated with the failure but I am dropping all the tables and while creation it fails. I will try to group them into a single container and post the result . thanks all
0 Likes 0 ·

1 Answer

nidheesh.r.pillai avatar image
nidheesh.r.pillai answered
What I am assuming here is that you have two separate containers - one for dropping the tables and other to create tables - filled with the necessary Execute-SQL Tasks. From the scenario you mentioned it looks like the table objects are not getting dropped properly in a single go, and hence a second execution is required and from there all goes well. Instead, what I will suggest is to design your Execute-SQL tasks such that the SQL statements expression does a DROP-CREATE of your tables inside those tasks. This will be in accordance with the Atomicity of the transaction's ACID properties, ensuring that either the entire drop and create transaction is done or none at all. Group all such similar Execute-SQL tasks into one Sequence container and life will be easy!
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

apple avatar image apple commented ·
Thanks will try out this and let you know, but we have almost 15 similar packages and only this package fails . Thanks a lot
0 Likes 0 ·
apple avatar image apple commented ·
Thanks Nideesh, I put everything in a single container and it did take care of the error. It will run in production this weekend and hopefully it will not give any error. Initially we never had any issue few weeks after deploying it started giving this error. Thanks a lot
0 Likes 0 ·
nidheesh.r.pillai avatar image nidheesh.r.pillai apple commented ·
Glad to help! Good luck for the weekend ahead.
2 Likes 2 ·