Join Access Data with SQL Data in SSIS

Question

Join Access Data with SQL Data in SSIS

v-swgar

SSC Enthusiast

Points: 121
More actions
July 15, 2011 at 2:50 am

#240931

Hi,
I have my Access table structure as follows
AccountID useralias
1 anm
2 ghy
3 reni
4 mani
I need to pass this AccountID Column and Useralias combination to AccountTeam table in SQL Server and fetch AccountTeamID
Account Team Table Structure
AccountID useralias AccountTeamID
1 anm 1
2 ghy 2
3 reni 3
4 mani 4
1 raj 5
1 rani 6
1 fella 7
If both the tables are in SQL server, i would do a join something like
SELECT
a.AccountID,at.AccountTeamID,a.UserAlias
FROM Account a
JOIN Accountteam at
on a.AccountID = at.AccountID
and a.UserAlias = at.UserAlias
I know how to do this in SQL, but not sure when both the data sources are different..
Please let me know if you need more details
Thanks in Advance

Viewing 3 posts - 1 through 2 (of 2 total)

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

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

You can use OLE DB sources to connect to both sources and fetch the data. Make sure you use ORDER BY queries and mark the join columns as sorted in the advanced editors of the source components. Then use a MERGE JOIN to perform your join in the SSIS dataflow.

(the MERGE JOIN needs sorted input, that's why you need the ORDER BY clauses)

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

v-swgar SSC Enthusiast Points: 121 More actions · Answer 2

Hi Koen,

Thanks so much for the reply.

we have around 2 lakh records in both Account and Account Team Tables,

hence when we run the query and perform a merge join , it takes long duration.

We need to take data from both Account and Account Team tables for only the Account ID and User alias combination, so that the time taken to execute this task would be reduced.