Need your advice - 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 2012 Forums

SSIS and Import/Export (2012)

Need your advice

Author

Topic

LearningSQLKid
Yak Posting Veteran

51 Posts

Posted - 2014-05-06 : 09:45:27

Hi Guys

I need your advice is designing a solution in SSIS 2012.

I have an oracle data source and I need to extract data from this source into SQL Server 2012 database. There are about 800 tables which I need to extract. I need your advice that what should be my approach

I have few option as below

1) Option 1:- In this option i can create a Master Package and this package will call the child packages one by one and each package will load the data in it table. The problem with this approach is that it will take lot of time to design 800 ssis packages (one ssis package for each table load)

2) Option 2:- I create one package and create dataflow task in it and load all the tables through dataflow task Again this is time consuming activty

3)Option 3: I can create a table In sql server database when i keep the table name and sql query; something below

RowNo TableName SQLQuery
1 Customer Select * from customer;
2 Products Select * from products;
3 Categories Select * from categories;

Now in ssis i create a foreach loop and place a script component task in there and in script component i do the bulkInsert for each table

What you guys think which one is more reliable. I am confused and i need your suggestions on this . Please help

Thanks heaps

Select Knowledge from LearningProcess

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2014-05-06 : 14:52:02

Check out this presentation - might be another option: http://sqlblog.com/blogs/allen_white/archive/2013/10/16/speaking-automate-your-etl-infrastructure-with-ssis-and-powershell.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-05-09 : 05:57:03

I would prefer 1. You dont need to create 800 ssis packages as such. There are ways to generate SSIS package programatically if functionality is similar like say using .NET code, BIML script etc

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