DTS - Problem With Fully Qualified Table Names

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 f开发者_运维问答irst 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

---

You refer to the problem table as a temp table, but it looks as though this is an actual table. I guess that you plan on dropping the table later in the package? After the package fails, have you verified that the table exists? Is the syntax for the table correct?

Also, your on 2005 now. Why not upgrade the package to SSIS? A log provider in SSIS can record much more than just the error message which would help you get down to what the real issue is.

---

Since it fails intermittently, the fact that it uses a fully qualified name is unlikely to be the problem. I would suspect the problem lies in some step above it and thus the table never gets created. Or is this table created using a select into statement rather than a create table? If so and there are no records to select into, that could cause an intermittent problem like this.

Do different people run the package under different user accounts rather than in a job? Perhaps one doesn't have the rights to create a table in that schema.

---

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.