Since I am new to SSIS. I got one task from my team. The task is
I have employee,department table in test1 and test2 database. I need to compare this two tables dynamically and i need to find the difference between two tables. For this i have used ForEachloop container in SSIS. What I did is i have created one test table in source with the name of emp and dept tables. I have passed this value as a recordset destination and for each loop container. Inside For loop container I have added one DFT to compare these tables. while execution it was giving the following error.
[OLE DB Source ] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The request for procedure 'emp' failed because 'emp' is a table object.".
please any one help me out to solve this issue or suggest me is there any approach.
the best way to do this in SSIS is to use a lookup task. Add a data flow task, inside add a oledb source to connect to one of the tables (say emp). then add a lookup task to ftech records from department and add relationships based on columns to be used for comparing. select required columns from lookup table in output. The records with nulls in these columns are ones present in employee table and missing from department and ones with valied value is present in both tables see http://www.sqlis.com/sqlis/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx