Inserting data into multiple table in one SSIS pac

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

SSIS and Import/Export (2005)

Inserting data into multiple table in one SSIS pac

Author

Topic

avlakshmi77
Starting Member

15 Posts

Posted - 2011-09-28 : 05:00:32

Hi

How to create SSIS package to insert data into multiple table with one SSIS package.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 11:23:41

with such limited detail provided, only thing we can tell is you can use a data flow task for achieving it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

avlakshmi77
Starting Member

15 Posts

Posted - 2011-09-29 : 03:43:19

Hi

I have a task to insert the Sales Details into the Destination database.
In Source I have Customer Details,Orders and Item details from 3 tables
From these details i have to insert into 3 different tables in the destination in a single SSIS package

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-29 : 08:06:34

you want to join together columns from different tables and insert? or you want to do 1- to - 1 inserts between source data and different destination tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

avlakshmi77
Starting Member

15 Posts

Posted - 2011-09-30 : 20:44:25

I want to join together from different tables and insert into the different tables.
tell me what are the controls used for this also

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-01 : 14:17:15

you can use lot of options

1. using data flow task with multiple oledb sources and series of lookups to get related data from other tables and finally dumping to destination using OLEDB destinations
2. using merge join to join data from various sources and putting into oledb destination
3. using execute sql task and writing sql query to join various tables and populating destination table. this can be used if destination as well as source databases are on same server.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

avlakshmi77
Starting Member

15 Posts

Posted - 2011-10-03 : 05:02:43

Thanks for your reply.
from your suggestion i used data flow task.
In that i used following components
Mutltiple ADO NET source -> For getting the data from different sources
one union all-> to place all data in a single point
one Multicast->to place in the different destination tables

For this it is working fine.
But Is there any performance issue to follow in this way?
If any issue please tell me the alternative way

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 05:21:45

how is it performing currently? is the tables sufficiently large? also why you're using ADO.NET source? whats your source system?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

avlakshmi77
Starting Member

15 Posts

Posted - 2011-10-03 : 08:16:31

i am checking in small size tables. no problem right now.
for big size table i din't check.my dought for large tables my way of approch is correct or not.
My source is Sqlserver. that's why i am using ADO NET source

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 08:23:51

if its sql server why not use OLEDB source?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

avlakshmi77
Starting Member

15 Posts

Posted - 2011-10-03 : 08:28:06

i read some where that for connecting sqlserver sources we can use ADO NET source.
OLEDB source purpose is to connect different type of sources.

so i am using ADO NET.
is there any problem for using ADO NET source?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 08:52:03

nope. but common way is to use OLEDB. Anything is fine so long as its performing well.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

avlakshmi77
Starting Member

15 Posts

Posted - 2011-10-04 : 00:30:51

thanks for your reply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 00:47:30

np

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

avlakshmi77
Starting Member

15 Posts

Posted - 2012-01-20 : 23:31:31

Hi

I created one ssis package. checked for 1000 records. it is working fine. here i getting whole data at a time.
But in live system having lakhs of records. Retriving the data from different server. Some times it is giving 'sql time out exception'.

Is there any way to solve this problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources