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
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!