I have a SQL DTS package running on a SQL Server 2005 database that, for the most part, works properly. However, sometimes it fails and I can't figure out why.
The package consists of an SQL task which creates a bunch of temp tables, a bunch of data transformations, then another SQL task at the end to drop the temp tables created in the first step.
The package fails every now and again because one of the temp tables referenced in a Transform Data Task can't be found, despite the table being one of the temp tables created in the first step.
The only difference I can see between the intermittently failing task and all the other Transform Data Tasks is the Table Name property for the Destination - it is fully qualified, ie. MyDatabase.dbo.TempTable, whereas all of the other tasks just have the table name specified as TempTable. I have no idea why this one is different, or how to fix it. I am assuming that this is the problem as it's the only thing that seems to be different between this one task and all of the others.
Also, if I manually execute the first step of the DTS to create the temp tables before executing the package in full, it always works.
Can anyone shed any light on what the problem may be here or how I can unqualify the destination table name?
Thanks
I found out what the problem was! Turns out that the Workflow Properties for the failing task were incorrect and didn't specify the "Create Temp Tables" task as a prerequisite, so every now and again the task was running before the associated destination table had been created. This wasn't happening often as the table was the second one to be created, so was generally present before the next task executed. All of the other tasks had the correct workflow properties, but for some reason this one had been missed out.