Periodically, I need to load tables with manually updated files. To automate this process I have created a package that creates a backup of the current tableA data; looks for the file and if there continues with a SQL task to truncate tableA and a data flow task to load tableA from the file.
I have an onerror event paired with the data flow task that truncates the tableA and reloads from the backup.
SQL
Truncate table [dbo].[JobClassANDI] Insert into[dbo].[JobClassANDI] Select * from [dbo].[JobClassANDI_BKP]
To test the onerror event I specifically cause an error. The data flow task continues processing, the onerror SQL task is processing and neither ever finish. I'm thinking it has something to do with the data flow task has the table locked and the onerror SQl task is trying to access it.
Is there a way i can remove the lock from the data flow task once it errors? Is something else happening?
I have also tried using the constraint failure path to execute the same sql task, but even when the data flow task generates the red X, the failure path does not execute.
