Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data Migration with SSIS in multiple steps Expand / Collapse
Author
Message
Posted Wednesday, April 3, 2013 2:39 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 1:32 PM
Points: 3, Visits: 73
I am new to SSIS.

I am planning to use SSIS for data migration. I have a big source table (record count: 35M). I would like to start the migration from old table to the new table before the actual release day. In that way, on the actual release day, the execution time is low.
How can I do that?

For example, today is April 03. And our actual release date is April 10. The source table has a record count of 35 Million (as of today). May be by April 10, it will grow up to 37 Million records.
Option 1: I can run the SSIS package on April 10, and it will have to take care of 37 Million records.
Option 2: Can I run the SSIS package on April 08 for records upto that point. and then run the package again on April 10 for the rest of the records?
If option 2 is possible, how can I do that?

Thanks in advance for your kind advise.


Post #1438576
Posted Wednesday, April 3, 2013 3:34 PM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:03 PM
Points: 1,480, Visits: 1,028
Depends,
Does the data get updated on any of the existing rows?
Does the Source table have a key column that you can use to tell if the data is in the new table or not?

If the data does get updated that makes your job a lot tougher. Because it limits what data you can copy ahead of time because if it is updated after you copy it that what do you do?

Have you practiced how long it takes to copy the table?
35 Million Rows Seems like a lot but you might be surprised how long it takes to copy if you make sure your SSIS Package is using Fast load.

Also Practicing may help you answer some other important questions like what to set the initial size of your database. This will prevent frequent autogrow events which would also slow the insert process down slightly.

If the data does not get updated, and it does have a key column. then you certainly can copy "old" rows over ahead of time. and just copy the rest on release night. Again, Try to practice so you don't run into any gotcha's on release night.
Post #1438600
Posted Wednesday, April 3, 2013 4:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 1:32 PM
Points: 3, Visits: 73
Thanks you very much Ray M for your kind reply with valuable advise.

[Ray M] Does the data get updated on any of the existing rows?
[Allen] No, the data will NOT be updated on the existing row.

[Ray M]Does the Source table have a key column that you can use to tell if the data is in the new table or not?
[Allen] The source table has an identity column which I plan to use to find out whether the data is in new table or not.

[Ray M]If the data does get updated that makes your job a lot tougher. Because it limits what data you can copy ahead of time because if it is updated after you copy it that what do you do?
[Allen] Since the data will not get updated on the existing row, I am safe.

[Ray M]Have you practiced how long it takes to copy the table?
[Allen] 68 minutes.
35 Million Rows Seems like a lot but you might be surprised how long it takes to copy if you make sure your SSIS Package is using Fast load.

[Ray M]Also Practicing may help you answer some other important questions like what to set the initial size of your database. This will prevent frequent autogrow events which would also slow the insert process down slightly.

If the data does not get updated, and it does have a key column. then you certainly can copy "old" rows over ahead of time. and just copy the rest on release night. Again, Try to practice so you don't run into any gotcha's on release night.
[Allen] Thanks for the advise. I plan to do testing more on this.

[Allen]
Question: How do I create the package that it checks whether data has been transferred or not.
Do I need to set some variable? Or some other thing? Thanks a million for your kind advise.
Post #1438611
Posted Thursday, April 4, 2013 11:08 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:03 PM
Points: 1,480, Visits: 1,028
You can design your package in a way to figure this out.

Use a sql task to query and stuff the resultset into a variable
IF EXISTS (SELECT * FROM NewDatabase..Table)
SELECT MAX(IDentityColValue) AS MaxVal FROM NewDatabase..Table
ELSE
SELECT 0 AS MaxVal


Set a variable in the package for the MaxVal.

Then in your data flow.
Source Query will be
SELECT <ColumnList> FROM OldDatabase..Table WHERE IdentityColValue > ?
And set the Parameter to feed in the MaxVal Variable.

Post #1438917
Posted Thursday, April 4, 2013 11:24 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 12:03 PM
Points: 1,480, Visits: 1,028
Also in your Destination Container make sure the Table Lock box is checked.

You may also want to look at removing all indexes from your destination table.
This may speed the load up. and then apply the indexes after.
If you can test this and see if it improves the time.

Here's a good article. Check out point 4, not all the items apply to your situation.
http://sqlcat.com/sqlcat/b/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx
Post #1438927
Posted Thursday, April 4, 2013 12:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 1:32 PM
Points: 3, Visits: 73
Thank you very much Ray M for your kind advise. I will utilize them and update you and the forum accordingly.
Really appreciate.
Regards, Allen.
Post #1438971
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse