data transfer with SSIS multiple left join with source table

Question

data transfer with SSIS multiple left join with source table

Megha P

SSCommitted

Points: 1976
More actions
April 9, 2014 at 2:13 am

#292076

Hi,
below is the query i am implementing in SSIS .Below records should be inserted in destination table
SELECTM.Email_ID,
M.Active,
B.CompanyServerID,
A.comapnyAccountID
FROM Mst_From_Email M
LEFT JOIN CompanyAccounts A ON M.Company_ID = A.companyID
LEFT JOIN CompanyServerMaster B ON M.POP3_User_Name = B.UserName
step 1: i have used one dataflow task,
In that used merge join control for left join to CompanyAccounts (join on int column)
for this i have done sorting with advance editor.sorted on INT column
step 2: done sorting for above result on varchar column .
step 3 : done sorting for CompanyServerMaster on varchar column
used merge join (left join) for CompanyServerMaster (join on varchar column).
data are not correct .
If in step 2 i do sorting on int primary key data, inserted correctly upto step 2
how to make second left join on varchar column?
i have attached my package's screenshot.
Thanks..
Megha

Viewing 12 posts - 1 through 11 (of 11 total)

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

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

What are the data sources?

The advanced editor does not 'do sorting' - for that you need a SORT transformation. And these are slow - better to sort at source if possible.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Megha P SSCommitted Points: 1976 More actions · Answer 2

Data sources are SQL command like

select * from Mst_From_Email where Company_ID = 1 and active = 1

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

Megha P (4/9/2014)
Data sources are SQL command like
select * from Mst_From_Email where Company_ID = 1 and active = 1

Then do the entire source selection in T-SQL.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Megha P SSCommitted Points: 1976 More actions · Answer 4

Hi,

I got the solution..

I need to use separate data source for each left join..

so i have used multicast for source table then done left join .

i have attached screenshot of SSIS package..

Thanks,

Megha

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

As long as your data volumes are low and it works ...

But otherwise you should consider doing ALL sorting at source and then just setting the IsSorted property preceding the joins. Remove those SORT transforms if you can, they perform badly.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Nevyn SSChampion Points: 14210 More actions · Answer 6

If all the tables are in the same source DB, why not just do your query in one source and send it to your transformation?

You seem to be taking tasks into SSIS that dont really belong there, and which sql is better at.

Or at least use lookups instead of merge joining.

Megha P SSCommitted Points: 1976 More actions · Answer 7

We are going to transfer data from denormalize db to normalized db.Currently both source and destination are on same server..

but once i complete creating packages for all tables , we will make destination to production server.

Thanks,

Megha

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

Megha P (4/10/2014)
We are going to transfer data from denormalize db to normalized db.Currently both source and destination are on same server..
but once i complete creating packages for all tables , we will make destination to production server.
Thanks,
Megha

Check if you can replace the MERGE JOINS with lookups.

If you are planning to transfer large data volumes, this package will kill your server.

(OK, a bit over dramatic, but it will use a lot of memory and it will be really slow)

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: 246962 More actions · Answer 9

And regardless of where those servers are, you can still get rid of SORT components if you decide to keep the Merge Joins.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

Phil Parkin (4/10/2014)
And regardless of where those servers are, you can still get rid of SORT components if you decide to keep the Merge Joins.

Good call. You can sort in the source components and remove these pesky blocking sorts.

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

Megha P SSCommitted Points: 1976 More actions · Answer 11

Thanks..Phil Parkin and Koen Verbeeck.

will do that.