How to transfer multiple tables from oracle to Sql server 2005 using SSIS

Question

How to transfer multiple tables from oracle to Sql server 2005 using SSIS

badam.manoj

Old Hand

Points: 353
More actions
November 19, 2010 at 2:31 am

#243067

Hi,
I'm trying to copy a single table from Oracle server to Sql server 2005 using SSIS
And i achieve this using one DataFlow task in the package.
Now the requirement has changed, i need to copy 4 tables from oracle to sql server
I'm trying in this way:
I have created a small table which contains the names of 4 oracle tables to copied
By adding a Execute Sql task I'm retrieving all these table names into a variable "user::Table_Names" (whose Data type is object)
And I kept this dataFlow Task (which i created for the single table) in the ForEachLoop task but i don't understand how to pass the table name in each iteration from that "user::Table_Name" variable.
here I'm using "Foreach from Variable Enumerator" in the collection tab of "ForEach loop Container".
I'm new to SSIS, Please provide a solution to resolve this issue.
Thanks
Manoj

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

Unless the tables are the same in structure (field names, types) and have exactly the same destination (table, field names), you'll need four separate dataflow tasks anyway.

If that's the case, it's probably not worth going down the foreach avenue - you're not making your life any easier.

Just build the four dataflows and then run them, in parallel if that makes sense in your situation, without using the FEL.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

badam.manoj Old Hand Points: 353 More actions · Answer 2

Hi Phil,

Thanks for your response

you left me no choice.. I'm creating 4 dataFlow tasks now

suggest me if there is any other approach which makes it easier

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

To do it more elegantly requires a dynamic dataflow task, which is not available out of the box.

You may find that this gives you a better and more scalable solution (for when the requirement becomes 64 tables!), but it's going to cost you ...

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

badam.manoj (11/19/2010)
Hi Phil,
Thanks for your response
you left me no choice.. I'm creating 4 dataFlow tasks now
suggest me if there is any other approach which makes it easier

You can also use just one dataflow task with 4 sources and 4 destinations. That way you can take advantage of parallellism.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246985 More actions · Answer 5

da-zero (11/19/2010)
badam.manoj (11/19/2010)
Hi Phil,
Thanks for your response
you left me no choice.. I'm creating 4 dataFlow tasks now
suggest me if there is any other approach which makes it easier
You can also use just one dataflow task with 4 sources and 4 destinations. That way you can take advantage of parallellism.

I guess it comes down to personal choice, but if I were going for parallelism in this situation, I would still use four dataflow tasks but put them (unconnected) in a Sequence container.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

tmitchelar Hall of Fame Points: 3391 More actions · Answer 6

This is soley based on personal preference, but I would create four separate packages...one for each table. I like having the option of being able to rerun tables individually - little more to it if they are all contained in the same package.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

Phil Parkin (11/19/2010)
I guess it comes down to personal choice, but if I were going for parallelism in this situation, I would still use four dataflow tasks but put them (unconnected) in a Sequence container.

Would using 4 seperate data flow not result in redundant validation and logging? (okay, since there are only 4 data flow, it wouldn't really matter, but I'm looking at the bigger picture here :-))

But you're right, it all comes down to personal choice. Just like a prefer Belgian beers over British beers any day of the week :-P;-) (thank god it's friday!)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 8

tmitchelar (11/19/2010)
This is soley based on personal preference, but I would create four separate packages...one for each table. I like having the option of being able to rerun tables individually - little more to it if they are all contained in the same package.

True true, you make a good point about restartability (or is there another decent word for this that ends with -bility?).

But then you can always place an Execute SQL Task with the transfer SQL statement into a for each loop container and keep some sort of iterator so that you know which tables are already transfered. This way you can keep it all in one single package. You lose the parallellism, but you reduce disk I/O.

But then again, personal preferences. (I hope the OP still has a clue about what we're talking about)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP