THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jorg Klein

Jorg Klein, Microsoft-only BI consultant from the Netherlands

Replication Services as ETL extraction tool

In my last blog post I explained the principles of Replication Services and the possibilities it offers in a BI environment. One of the possibilities I described was the use of snapshot replication as an ETL extraction tool:
“Snapshot Replication can also be useful in BI environments, if you don’t need a near real-time copy of the database, you can choose to use this form of replication. Next to an alternative for Transactional Replication it can be used to stage data so it can be transformed and moved into the data warehousing environment afterwards.
In many solutions I have seen developers create multiple SSIS packages that simply copies data from one or more source systems to a staging database that figures as source for the ETL process. The creation of these packages takes a lot of (boring) time, while Replication Services can do the same in minutes. It is possible to filter out columns and/or records and it can even apply schema changes automatically so I think it offers enough features here. I don’t know how the performance will be and if it really works as good for this purpose as I expect, but I want to try this out soon!”

Well I have tried it out and I must say it worked well. I was able to let replication services do work in a fraction of the time it would cost me to do the same in SSIS.
What I did was the following:

  1. Configure snapshot replication for some Adventure Works tables, this was quite simple and straightforward.
  2. Create an SSIS package that executes the snapshot replication on demand and waits for its completion.
    This is something that you can’t do with out of the box functionality. While configuring the snapshot replication two SQL Agent Jobs are created, one for the creation of the snapshot and one for the distribution of the snapshot. Unfortunately these jobs are  asynchronous which means that if you execute them they immediately report back if the job started successfully or not, they do not wait for completion and report its result afterwards. So I had to create an SSIS package that executes the jobs and waits for their completion before the rest of the ETL process continues.

Fortunately I was able to create the SSIS package with the desired functionality. I have made a step-by-step guide that will help you configure the snapshot replication and I have uploaded the SSIS package you need to execute it.

Configure snapshot replication

0 
The first step is to create a publication on the database you want to replicate.
Connect to SQL Server Management Studio and right-click Replication, choose for New.. Publication…

1 
The New Publication Wizard appears, click Next

2
Choose your “source” database and click Next

3
Choose Snapshot publication and click Next

4 
You can now select tables and other objects that you want to publish

5
Expand Tables and select the tables that are needed in your ETL process

6
In the next screen you can add filters on the selected tables which can be very useful. Think about selecting only the last x days of data for example.

7
Its possible to filter out rows and/or columns. In this example I did not apply any filters.

8
Schedule the Snapshot Agent to run at a desired time, by doing this a SQL Agent Job is created which we need to execute from a SSIS package later on.

9
Next you need to set the Security Settings for the Snapshot Agent. Click on the Security Settings button.

10 
In this example I ran the Agent under the SQL Server Agent service account. This is not recommended as a security best practice. Fortunately there is an excellent article on TechNet which tells you exactly how to set up the security for replication services. Read it here and make sure you follow the guidelines!

11 
On the next screen choose to create the publication at the end of the wizard

12
Give the publication a name (SnapshotTest) and complete the wizard

13 
The publication is created and the articles (tables in this case) are added


Now the publication is created successfully its time to create a new subscription for this publication.

14 
Expand the Replication folder in SSMS and right click Local Subscriptions, choose New Subscriptions

15 
The New Subscription Wizard appears

16 
Select the publisher on which you just created your publication and select the database and publication (SnapshotTest)

17 
You can now choose where the Distribution Agent should run. If it runs at the distributor (push subscriptions) it causes extra processing overhead. If you use a separate server for your ETL process and databases choose to run each agent at its subscriber (pull subscriptions) to reduce the processing overhead at the distributor.

18
Of course we need a database for the subscription and fortunately the Wizard can create it for you. Choose for New database

19 
Give the database the desired name, set the desired options and click OK

20
You can now add multiple SQL Server Subscribers which is not necessary in this case but can be very useful.

21 
You now need to set the security settings for the Distribution Agent. Click on the …. button

22
Again, in this example I ran the Agent under the SQL Server Agent service account. Read the security best practices here

23 
Click Next

24 
Make sure you create a synchronization job schedule again. This job is also necessary in the SSIS package later on.

25
Initialize the subscription at first synchronization

26
Select the first box to create the subscription when finishing this wizard

27
Complete the wizard by clicking Finish

28
The subscription will be created

29
In SSMS you see a new database is created, the subscriber. There are no tables or other objects in the database available yet because the replication jobs did not ran yet.

Now expand the SQL Server Agent, go to Jobs and search for the job that creates the snapshot:

 30
Rename this job to “CreateSnapshot”

Now search for the job that distributes the snapshot:

 31
Rename this job to “DistributeSnapshot”

Create an SSIS package that executes the snapshot replication

We now need an SSIS package that will take care of the execution of both jobs. The CreateSnapshot job needs to execute and finish before the DistributeSnapshot job runs. After the DistributeSnapshot job has started the package needs to wait until its finished before the package execution finishes.
The Execute SQL Server Agent Job Task is designed to execute SQL Agent Jobs from SSIS. Unfortunately this SSIS task only executes the job and reports back if the job started succesfully or not, it does not report if the job actually completed with success or failure. This is because these jobs are asynchronous.

The SSIS package I’ve created does the following:

  1. It runs the CreateSnapshot job
  2. It checks every 5 seconds if the job is completed with a for loop
  3. When the CreateSnapshot job is completed it starts the DistributeSnapshot job
  4. And again it waits until the snapshot is delivered before the package will finish successfully

33

Quite simple and the package is ready to use as standalone extract mechanism. After executing the package the replicated tables are added to the subscriber database and are filled with data:

 32

Download the SSIS package here (SSIS 2008)


Conclusion

In this example I only replicated 5 tables, I could create a SSIS package that does the same in approximately the same amount of time. But if I replicated all the 70+ AdventureWorks tables I would save a lot of time and boring work! With replication services you also benefit from the feature that schema changes are applied automatically which means your entire extract phase wont break. Because a snapshot is created using the bcp utility (bulk copy) it’s also quite fast, so the performance will be quite good.

Disadvantages of using snapshot replication as extraction tool is the limitation on source systems. You can only choose SQL Server or Oracle databases to act as a publisher.

So if you plan to build an extract phase for your ETL process that will invoke a lot of tables think about replication services, it would save you a lot of time and thanks to the Extract SSIS package I’ve created you can perfectly fit it in your usual SSIS ETL process.

Published Saturday, March 12, 2011 11:42 AM by jorg

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Marlene said:

Hi

I am trying to get your SSIS package, but somehow i am stupid, and can not see the design and go into the steps of your Extract.dtsx.

How can i get to the design of it?

Thanks!

marlene

March 31, 2011 3:36 AM
 

jorg said:

Hi Marlene,

I have just checked the package again and I can open it and see the design without problems. Please note that it's a SSIS 2008 package so you wont be able to open it in SSIS 2005!

--Jorg

March 31, 2011 6:51 AM
 

Scrape Web Pages said:

Hi,

I like this site. Keep it working every day.

September 27, 2011 12:48 AM
 

Aniket said:

Hi Jorg,

Nice blog. I have a Source system which is providing Real time production data on the equipments at the Mine. Some tables are unloaded and loaded again, some tables are updated , some table deleted a slot and reloads. I am using T-SQL to bring the data from these tables to a staging area. For such a scenario which replication should I use as I am copying the exact same data of the source to the stage. Currently I employ methods to check latest timestamp record at staging and bring from source only those records which are greater than timestamp. Also for code table I use "truncate insert into " mechanism.

Please advise me on this

November 6, 2011 2:15 AM
 

Daniel said:

I like the post very much!

Could you tell something about the performance when using an Oracle source? (Keeping in mind the Oracle Connector by Attunity with it's blazing performance!)

December 30, 2011 12:18 PM
 

Karthik Vadla said:

Hi Jorg,

I have downloaded the SSIS package, but not able to view the design. Can you please provide me .dtsx package with design.

Thanks

Karthik Vadla

December 18, 2012 4:13 AM
 

ssc said:

can you please share the sample ssis package for the above.

Thanks in advance...

August 11, 2014 7:18 AM

Leave a Comment

(required) 
(required) 
Submit

About jorg

Jorg Klein, Microsoft-only BI consultant from the Netherlands, blogging about BI on SQL Server with a focus on SSIS.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement