January 26, 2008 at 6:21 am
Hi.
I have to transfer periodically (every 5 minutes) data from sql server 2000 to a sql server 2005.
I try to use SQL Server Integration Services for do this but I have doubt.
When the process finish to transfer data to SQL2005 it have to update a "state" column, only for the trasfered data, of SQL2000 table from '1' to '2'.
I don't want to use for each loop because it's very slow. I want to do this process all in a data flow.
But if I do all in a data flow I have to read data from SQL 2000, put it in SQL 2005 and then update data in SQL 2000.
Who can I do this with SSIS ?
Thanks.
January 26, 2008 at 9:25 am
You certainly can. You just have to introduce multiple steps into your SSIS project. You're going to want to read up on it. Actually Andy Leonard put up a good article this week on SSIS, with a good example of a multi-stage SSIS:
http://www.sqlservercentral.com/Forums/Topic447281-208-1.aspx
As well, David Bird has a series of articles on How-to in SSIS. I'm thinking you want Part II, but they're all good:
http://www.sqlservercentral.com/articles/Integration+Services/61774/
And - if that's not enough - there are 100+ more articles I haven't even mentioned right here:
http://www.sqlservercentral.com/Articles/SQL+Server+2005/SQL+Server+2005+-+SSIS/
--------------------------------------------------------------------
As so your current challenge:
As a thought - just so that you don't run into any disconnect as to what you're transerring, you might care to introduce another status: transferring. That advantage is that you can ensure that you only update those you've transferred, and you don't miss stuff that might be getting updated WHILE you're running the transfer.
So the process would look like:
1. run an update for all records you're GOING to transfer from 1 (not transferred) to -1 (transferring). Use whatever criteria you initially had to pick them out.
2. using ONLY the "transferring" criteria (i.e. don't use the initial set of criteria anymore), do your perform on all of those with that status
3. Once the transfer is done successfully, THEN update the status to 2 (transferred). If it fails - then you can either retry those records, or just revert the status back to not transferred.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 28, 2008 at 1:05 am
Thank you for your reply.
I'm going to test your solution....:D
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply