Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 181, Visits: 1,445
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 (7 views, 21.77 KB)
Post #1559823
Posted Wednesday, April 9, 2014 2:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:56 AM
Points: 5,101, Visits: 11,901
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.
Post #1559830
Posted Wednesday, April 9, 2014 3:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 181, Visits: 1,445
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: Today @ 1:56 AM
Points: 5,101, Visits: 11,901
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.
Post #1559843
Posted Wednesday, April 9, 2014 4:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 181, Visits: 1,445
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 (3 views, 26.31 KB)
Post #1559862
Posted Wednesday, April 9, 2014 6:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:56 AM
Points: 5,101, Visits: 11,901
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.
Post #1559896
Posted Wednesday, April 9, 2014 1:26 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 8:15 AM
Points: 628, Visits: 2,129
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
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:42 AM
Points: 181, Visits: 1,445
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:15 AM
Points: 13,017, Visits: 10,800
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 LessThanDot.

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: Today @ 1:56 AM
Points: 5,101, Visits: 11,901
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.
Post #1560284
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse