August 5, 2014 at 8:27 am
I have a package that performs the following steps in order.
1. Execute SQL task drops and recreates all staging tables and indecies. The indecies are named the same as the permanent tables with "_staging" concatenated at the end of the name.
2. Data flow tasks load the staging tables
3. Execute SQL task
a.renames the permanent tables and concatenates "_BU" to both the table names as well as the indicies.
b. Rename the staging tables by dropping the "_staging" from the end of the table name as well as the index names.
c. Rename the backup tables by replacing "_BU" with "_staging" to both the table names as well as the index names.
4. Package completes.
My issue is that if the package fails after renaming the staging table by removing the "_staging" from the table name but before renaming the index (index name still has "_staging" at the end) the package fails when step 1 drops and recreates the table when it tries to create the index with "_staging" at the end of the index name because the index in the perm table still has an index with that name in it.
My question: would setting just one transaction at the package level roll back everything from the beginning to avoid this error?
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply