SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 best approach for data extract
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ade
Starting Member

United Kingdom
9 Posts

Posted - 06/17/2013 :  07:39:37  Show Profile  Reply with Quote
Hi

I could do with some help/suggestions for the approach I should take to improve a database extract I perform. Not asking for a complete solution, just some good advice on the approach I should spend my time learning/developing....

I have read-only access to a (third party's) mysql database which I import into sql server 2012 (my reporting database/server). Currently I do this by dropping each table and then recreating it and re-importing all the data. The database is very large and I would value some suggestions on how I could better this approach to insert only new rows or those that have been updated in the mysql tables.

Any help would be much appreciated!

Regards

Adrian

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 06/17/2013 :  08:08:10  Show Profile  Reply with Quote
Is there any audit columns(CreateTime, ModifiedDate columns) in MySQL tables?

--
Chandu
Go to Top of Page

Ade
Starting Member

United Kingdom
9 Posts

Posted - 06/17/2013 :  09:01:12  Show Profile  Reply with Quote
Hi Chandu

no! There are no audit columns and getting them added is not an option available to me.

Thanks
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2170 Posts

Posted - 06/17/2013 :  09:57:19  Show Profile  Reply with Quote
You can use the following methods also...
Method1:
1) Create Linked Server Between MySQL and SQL Server
2) Then you can apply DML Operation to reflect the changes

Method2:
Use OPENQUERY() distributed function to reflect the changes...

References:
http://www.codeproject.com/Articles/29106/Migrate-MySQL-to-Microsoft-SQL-Server
http://www.packtpub.com/article/mysql-linked-server-on-sql-server-2008


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/17/2013 :  10:04:12  Show Profile  Reply with Quote
since this is posted in SSIS forum, you can do this in SSIS too.

But again for identifying the incremental data you should ideally have a primary key of audit column. Even otherwise you should have a combination of columns which can identify a unique row.
Once thats there you can use Slowly Changing Dimension Wizard or a combination of lookup task and conditional task to do the incremental data merge.

you could also maintain a separate control table to monitor the process as well as capture last combintaion of column value to identify increments if they follow a sequence

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Ade
Starting Member

United Kingdom
9 Posts

Posted - 06/17/2013 :  11:36:01  Show Profile  Reply with Quote
Hi

The linked server has been in place for ages, every table has a primary key column. What I'm interested in is moving away from my current process which drops all my reporting tables, then re-imports everything, and move onto a process which only imports new rows or rows that have had some data modified.

I figured this would be a fairly common request and pretty standard options would exist in SSIS to achieve this, any ideas?

Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/17/2013 :  11:53:09  Show Profile  Reply with Quote
quote:
Originally posted by Ade

Hi

The linked server has been in place for ages, every table has a primary key column. What I'm interested in is moving away from my current process which drops all my reporting tables, then re-imports everything, and move onto a process which only imports new rows or rows that have had some data modified.

I figured this would be a fairly common request and pretty standard options would exist in SSIS to achieve this, any ideas?

Thanks


see my post above on tasks in SSIS you can use

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Ade
Starting Member

United Kingdom
9 Posts

Posted - 06/19/2013 :  08:33:24  Show Profile  Reply with Quote
Visakh

which part of your links are you suggesting I pay attention to? They seem to be tutorials on setting up linked servers - i set the linked server up a long time ago.

thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/19/2013 :  10:50:16  Show Profile  Reply with Quote
quote:
Originally posted by Ade

Visakh

which part of your links are you suggesting I pay attention to? They seem to be tutorials on setting up linked servers - i set the linked server up a long time ago.

thanks


i didnt post any links

I was suggesting use of Slowly Changing Dimension Wizard or a combination of lookup task and conditional task to do the incremental data merge

http://www.bimonkey.com/2009/07/the-slowly-changing-dimension-transformation-part-1/

http://jahaines.blogspot.in/2009/09/sss-performing-upsert.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Ade
Starting Member

United Kingdom
9 Posts

Posted - 06/20/2013 :  09:48:06  Show Profile  Reply with Quote
Hi Visakh

You're quite right - sorry I was looking at bandi's post! Thanks for the suggestion and the links, very interesting and I've plenty to go on now.

Regards

Adrian
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 06/21/2013 :  02:19:10  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000