Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


data transfer with SSIS multiple left join with source table


data transfer with SSIS multiple left join with source table

Author
Message
Megha P
Megha P
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 2501
Hi,

below is the query i am implementing in SSIS .Below records should be inserted in destination table

SELECT   M.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
Attachments
1.png (9 views, 21.00 KB)
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8300 Visits: 19448
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Megha P
Megha P
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 2501
Data sources are SQL command like

select * from Mst_From_Email where Company_ID = 1 and active = 1
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8300 Visits: 19448
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Megha P
Megha P
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 2501
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
Attachments
2.png (8 views, 26.00 KB)
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8300 Visits: 19448
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Nevyn
Nevyn
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 3149
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
Megha P
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 Visits: 2501
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
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16366 Visits: 13199
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)



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8300 Visits: 19448
And regardless of where those servers are, you can still get rid of SORT components if you decide to keep the Merge Joins.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search