How to mimic a transaction when MSDTC isn't available
-
I need to load data from an SQL 2000 server with MSDTC disabled into a set of staging tables in an SQL 2008 database. One of the tables is a "control" table that contains metadata about each other table in the database (row count, last updated, etc). Only when the actual data satisfies the expectations defined by the metadata (i.e. actual row count equals expected row count) do I want to retain the data in the staging tables.
Basically, I need to mimic a transaction without actually using a real transaction. I realize it would be sooo much easier to just enable MSDTC but unfortunately this is not an option.
I want to avoid creating a set of staging tables to hold the data, validate it, then move it into the existing staging tables because there actually already are two sets of staging tables to deal with. I didn't write this bloody thing, but let's all agree that it wasn't written very well.
The current solution uses a set of Staging tables and a set of "Previous" Staging tables to establish how the data has changed and to perform the required transformations. The transformations populate yet other tables used by the application. So adding a third set of "Temporary" Staging tables doesn't really sit well with me.
So what are my options? I've read that temp tables don't play well with SSIS packages, and since I'm dealing with SQL 2000 table variables aren't a viable option either. Is there some construct in SSIS that could support this.
Quite obviously, I am an SSIS novice and desperately need some help constructing a decent solution.
Thanks!
-
There is no solution or "trick" in SSIS that provides behavior similar to MSDTC. The approach you have described is the right approach based on the requirements you have.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Thanks for the response. I had a really tough time summarizing my issue and now that I read over it, I can see I wasn't really clear about my specific issue.
What I really want to know is, how can I avoid using a set of staging tables? I mean, there are staging tables, but they will contain data that I don't want to overwrite until I can confirmed the data has been transferred properly (via comparing actual row counts to those specified in the "control" metadata).
I'm currently looking at maybe just reading the data into memory using either an SQL Server Destination or OLE DB Destination. There are 6 tables in total with around 350,000 rows.
Thoughts?
-
You may try using temporary tables, which may work or not. I would recommend this article, which contains useful information.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
You can mimic a transaction in SSIS by using an Execute SQL task at the beginning of your package with the command BEGIN TRAN.
At the end of the control flow, you place another SQL Task with the command COMMIT.
Make sure the property RetainSameConnection of the connection manager is set to true.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Oh yeah, I forgot: SSIS does play nicely with temp tables, you just need to use the RetainSameConnection property I mentioned earlier.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
My 1.5 cents:
Consider using a LINKED Server to the 2000 machine.
Write SPROCS to do the actual work (with TRANSACTIONS if you like).
SPROCS can be evoked from SSIS via an EXEC SQL Task.
Viable? Or...not?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply