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