January 4, 2010 at 11:53 am
Hello Everyone
I am working with a SQL 2000 DTS Package. I am in the process of writing all the packages into SQL 2005 SSIS packages.
I was told that there are some of the packages that are not in use, and all of the code is in stored procedures that are fired off from the Schedule Jobs. This is fine. I was told the other developers did this because of the Temp tables being used in the DTS Package were not there once they were created.
In one step of the package a global temp table is created and filled with data. In a totally separate step within the same package, that temp table does not exist. What is up with that? I am at the understanding that in DTS, a temp table will be available until the end of the package. And especially a Global Temp Table.
It has been many years since I last worked heavily with DTS.
Can anyone clarify this for me?
Thanks in advance
Andrew SQLDBA
January 4, 2010 at 12:18 pm
This really isn't a DTS issue. A global temporary table is a global temporary table no matter where you create it from. It still must follow the rules and behaviors of a global temporary table. Global temp tables, just like local temp tables, are scoped to the session that creates them. As soon as all session using them are disconnected from the instance, the table is dropped. What you are probably seeing here is that the DTS package is creating and populating the table with one logon. That session is then logged off before the next step attempts to use the table. Since the table is not in use, it must be dropped.
I would recommend replacing the global temp table with a static staging table. You can create it at the beginning of the package, use it, then drop it at the end of the package.
Better yet, if you are re-writing the packages in SSIS, scrap the idea of using staging/temp tables and do all of your ETL work inside a data flow. What used to take temp/staging tables in DTS can now be done by working the data through the data flow pipeline.
January 4, 2010 at 12:27 pm
Thanks, I plan on doing just that in SSIS
I was just trying to figure out what may be causing the DTS issue. I was thinking if they had created a parent package, created all the temp tables, then called some child packages, and then come back to the parent to complete. Something like that, but was more of just curious really as to why they could not get them to work properly.
Thank you for your advice.
Andrew SQLDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply