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 AccountTeam Table Structure AccountID useralias AccountTeamID 1 anm 1a1 2 ghy 2b2 3 reni 3c3 4 mani 4d4 1 raj 5e5 1 rani 6f6 1 fella 7g7 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 dont know how to achieve this when AccountID and Useralias is in Access db and Accountteamid has to fetch from Sqlserver db Acctountteam table. Please help me to know the tranformation used to achieve this in SSIS .. Thanks in Advance

You can do this with a join in the datasource if both tables are in Access DB. You can do this with a merge join in SSIS. Notice: both datasources have to be sorted or a sort component has to place right before the merge join You can use a lookup task to add the TeamID to your recordset

Look at my answer at your question here at SQLServerCentral: http://www.sqlservercentral.com/Forums/Topic1142287-147-1.aspx I'm too lazy to type it twice :)MCTS, MCITP - Please mark posts as answered where appropriate. Answer #1: Have you tried turning it off and on again? Answer #2: It depends...

Hi, I have tried the above said process but, the problem is I have more than 4L records in Accountteam table so dumping all the data through Look up is a time taking proceess.. I have tried OpenRowset to search the data in Accountteam table of sqlserver with the data in AccountID and useralias of Accessdab table. Select AccountTeamID From AccountTeam AT Inner Join Account A on AT.AccountID=A.accountid Inner Join OPENROWSET('Microsoft.ACE.OLEDB.12.0','Accessdb.mdb'; 'admin';'',Account) Acc On A.AccountID=Acc.AccountID and A.UserAlias.Acc.Useralias ; But I got the exception that "Microsoft.ACE.OLEDB.12.0" is not registered. Please provide a solution for this.

More than 4L? What does this mean? Did you already tried how much time this will last? And as far as I understand you will import both tables, than you can do the update in SQL Server.