Replication using SSIS - SQL Server Forums

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 2008 Forums

SSIS and Import/Export (2008)

Replication using SSIS

Author

Topic

Netjunkie
Starting Member

17 Posts

Posted - 2011-03-23 : 02:49:32

Hi Guys

I have a requirement to replicate a list of selected tables from my production database to reporting using SSIS. For various infrastructure reasons I cannot use SQL Replication.

Now, do i have to build one package for each table and integrate them into a master package or is there a way I can build one template package and pass the table names as a variable to the template?

Please suggest.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-03-24 : 16:41:20

you can do it in one ssis package.
1. but how are you planning on sending in the table name as parm? do u have a ui for running the ssis package or manual?
2. will the list of tables names to be replicated change from time to time or is it all of the tables you want to replicate?

If you don't have the passion to help people, you have no passion

Netjunkie
Starting Member

17 Posts

Posted - 2011-03-25 : 02:07:24

passing the table name as a param was just a thought.. I am not really sure if that is possible.

The list of tables will not change over time. They are 175 tables out of 500+ tables.
How can i do it in one package?

There is no UI to run the package. The idea is to schedule the package to run using SQL jobs.

please let me know

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-03-26 : 23:59:40

1. you could create an xml file that you loop through that has the name of the tables


     <tables>
       <table>Widgets<table>
       <table>Trinkets<table>
       <table>chicken wings<table>
     </tables>

2. A table driven method. create a table you loop through

While looping on object nodes, assign table name to local variable, concatenate the tsql command as expression

then execute that conctenated value

MERGE dbo.table target
(
)

etc

3. or create a sproc that takes care of this which will be a dynamic sproc that builds all your parts
but dynamic query is not recommended on this site due to security issues and maintenance issues

i would try these different methods

3.

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources