Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

data transfer with SSIS multiple left join with source table Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2014 2:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 16, 2016 12:02 AM
Points: 304, Visits: 2,501
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


  Post Attachments 
1.png (9 views, 21.77 KB)
Post #1559823
Posted Wednesday, April 9, 2014 2:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 7,948, Visits: 19,043
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

Just when you think your code is idiot-proof, the world invents a better idiot.
Post #1559830
Posted Wednesday, April 9, 2014 3:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 16, 2016 12:02 AM
Points: 304, Visits: 2,501
Data sources are SQL command like

select * from Mst_From_Email where Company_ID = 1 and active = 1
Post #1559838
Posted Wednesday, April 9, 2014 3:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 7,948, Visits: 19,043
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

Just when you think your code is idiot-proof, the world invents a better idiot.
Post #1559843
Posted Wednesday, April 9, 2014 4:40 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 16, 2016 12:02 AM
Points: 304, Visits: 2,501
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


  Post Attachments 
2.png (8 views, 26.31 KB)
Post #1559862
Posted Wednesday, April 9, 2014 6:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 7,948, Visits: 19,043
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

Just when you think your code is idiot-proof, the world invents a better idiot.
Post #1559896
Posted Wednesday, April 9, 2014 1:26 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, December 3, 2015 10:32 AM
Points: 940, Visits: 3,149
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.
Post #1560161
Posted Thursday, April 10, 2014 12:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, November 16, 2016 12:02 AM
Points: 304, Visits: 2,501
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
Post #1560266
Posted Thursday, April 10, 2014 1:47 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: 2 days ago @ 6:23 AM
Points: 15,499, Visits: 13,162
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
Post #1560277
Posted Thursday, April 10, 2014 2:27 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:49 PM
Points: 7,948, Visits: 19,043
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.

Just when you think your code is idiot-proof, the world invents a better idiot.
Post #1560284
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse