best approach for data extract

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SSIS and Import/Export (2012)

best approach for data extract

Author

Topic

Ade
Starting Member

9 Posts

Posted - 2013-06-17 : 07:39:37

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-17 : 08:08:10

Is there any audit columns(CreateTime, ModifiedDate columns) in MySQL tables?

--
Chandu

Ade
Starting Member

9 Posts

Posted - 2013-06-17 : 09:01:12

Hi Chandu

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

Thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-17 : 09:57:19

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-17 : 10:04:12

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

Ade
Starting Member

9 Posts

Posted - 2013-06-17 : 11:36:01

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-17 : 11:53:09

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

Ade
Starting Member

9 Posts

Posted - 2013-06-19 : 08:33:24

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 10:50:16

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

Ade
Starting Member

9 Posts

Posted - 2013-06-20 : 09:48:06

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 02:19:10

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources