SSIS Best Practice #1 - Avoid using drop & create table in SSIS

Out of sudden, one of my agent job has fail and the error message was

Description: Failed to open a fastload rowset for "[dbo].[************]". Check that the object exists in the database.

In a controlled production environment, no single one could drop a table without any testing and checking. So what causes the error?

Trace back to the starting failure point.

Time-out occurred while waiting for buffer latch type 2 for page (11:11042329), database ID 11

This error message was actually due to SQL Server was too busy to create table which later causes the table seems to be missing from SSIS point of view.

In this case, i had to create the table manually due to SSIS would validate the package before executing it.

After further understanding, the reason of using Drop and Create is to reset the identity column back to 1.

In the conclusion, i highly advise that try not to use Drop and Create method in SSIS, you may try using Truncate Table statement; which also will reset identity column.