SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 SSIS and Import/Export (2012)
 Reg - For Ecah Loop Container task
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vinodhg25
Starting Member

7 Posts

Posted - 01/20/2014 :  04:24:06  Show Profile  Reply with Quote
Hi Every one,

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 [1]] 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.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/20/2014 :  06:55:25  Show Profile  Reply with Quote
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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

vinodhg25
Starting Member

7 Posts

Posted - 01/20/2014 :  08:42:28  Show Profile  Reply with Quote
my requirement is i need to add tables dynamically for comparison...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/21/2014 :  05:44:30  Show Profile  Reply with Quote
quote:
Originally posted by vinodhg25

my requirement is i need to add tables dynamically for comparison...


then best thing is to use tablediff utility
https://www.simple-talk.com/sql/sql-tools/sql-server-tablediff-utility/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000