CODEWITHSUNDEEP
ssis
Jintu
Jintu

Reputation: 121

Join multiple tables using single transformation using SSIS 2008

I am new to SSIS, my task is to join multiple tables and insert it into a destination table.

Source Tables are from SQL Server and Destination is again SQL Server.

All the tables have relationship and just need to join and insert the data into SQL Server table again.

I want to do it with SINGLE transformation items in SSIS.

Please give me the steps to acheive this.

Upvotes: 2

Views: 19478

Answers (3)

Mike Honey
Mike Honey

Reputation: 15037

I would start with an OLE DB Source that only selects columns from your fact / transactional / most detailed table.

Then I would add a Lookup transformation for each reference table.

Finally I would add an OLE DB Destination to insert the data into the destination table (ideally with Fast Load and Table Lock).

This design provides the best runtime performance and ensures each component is simple and fast.

Upvotes: 0

Chielus
Chielus

Reputation: 632

If the source tables are located in 1 single SQL server instance, you should use the 'OLEDB source' or 'SQL Server source'. In the data access method listbox, choose SQL command and write or build your join query.

The performance of this solution is much better then by using merge join transformations

Upvotes: 0

praveen
praveen

Reputation: 12271

One way is to directly use an oledb command and write your sql by mapping the columns (Column Mappings Tab)

or

you can use a merge join transformation .Sort your OLEDB sources .Right click on the component Go to the Show Advanced Editor and in the Input Output Properties select one of your output column and change sort key option from 0 to 1.Now use a merge join component and map the common columns as Join Key and select the columns which you need as output . Use an OLEDB destination to map the output columns from the above components and dump the data .

Check this article

Both the above 2 techniques uses only 1 component for performing joins

Upvotes: 5

Related Questions