At one of my customers they wanted me to load data into a vital table during daytime. The main problem is that this table is sometimes used in other SSIS packages. Also during daytime. The SSIS package that fills this table is a trunc / fill job.
So I started working on a technique to load data into another table and then switch names. To make this solution more robust I also added a retry logic and a delay.
You might want to change the script so that it fits your environment and needs 🙂
So your SSIS package will fill the table “MainTable_swap”. Then you can add a SQL Task at the end that executes the script below.
Declare @retries int = 0 Declare @numberOfRetries int = 4 RETRY: -- Label RETRY BEGIN TRANSACTION BEGIN TRY EXEC sp_rename 'MainTable', 'MainTable_switch' EXEC sp_rename 'MainTable_swap', 'MainTable' EXEC sp_rename 'MainTable_switch', 'MainTable_swap' COMMIT TRANSACTION END TRY BEGIN CATCH ROLLBACK TRANSACTION IF @retries < @numberOfRetries BEGIN print 'Failed... retry...' Set @retries = @retries + 1 WAITFOR DELAY '00:05:00.00' -- Wait for 5 m GOTO RETRY -- Go to Label RETRY END ELSE BEGIN RAISERROR (N'Could not rename table... giving up!', 10, 1, N'number', 5); select 1/0 END END CATCH
The “select 1/0” is to force SQL Task in SSIS to fail if the script fails. It did not response to the RAISEERROR.
Feel free to drop me an email if you have an improvment to the script