All, I have table A in SQL Server and table B in Teradata, basically I want to determine if all of the data in the sql server table A is in the teradata table B. If the record doesn't exist I want to write it out to either a file or something that can be reported on. Is there a way to do this in SSIS? MS

Yes, there is. You should use Lookup Transformation: 1- Add a new Data Flow task to the control flow tab of your package; 2- Right-click on it and select Edit; 3- Add an OLE DB Source to the Data Flow tab. Right-click and select Edit; 4- Select right Connection Manager in the OLE DB Connection Manager drop-down, and create one to point to yor instance of SQL Server if you don't have any; 5- Set Data Access Mode to Table or View and then select the table you want to compare; 6- Add a new Lookup transformation to the data flow tab. get the green arrow coming out of OLE DB source and connect it to Lookup transformation. right click on Lookup transformation and select Edit; 7- In the General tab, in "Specify how to handle rows with no matching entries" select "Redirect rows to no match output"; 8- In OLE DB Connection manager, select approprate connection that points to the TeraData database, and then select the table you want to do comparation against from Use a Table or View drop-down; 9- Int he Columns tab, map the columns that should be compared to see whether a new value is added to the source table or not. Usually you need to map the PK columns in the 2 tables to get the new records added to the source table; 10- Don't need specific setting for the other 2 tabs; 11- add an OLE DB source to the data flow tab. Get the green output of Lookup transfoamation and connect it to OLE DB destination. In the new window opened, select Lookup No Match Output; 12- Double-click on OLE DB Destination, and configure it to point to the table in Teradata database. Hope this decribes everything you need. Please mark as answer if this helps. Thank you. http://thebipalace.wordpress.com

Great steps, I will give it a try...can you tell me if there is a way to pass parameters to the data source in the data flow?? I have the select statement actually in a variable, both for SQL Server table and Teradata table that just pulls the columns needed. Thanks.MS

Try with SSIS Checksum Transformation/R.

Yes, you can pass parameters to your SSIS package easily. Check the following URL: http://msdn.microsoft.com/en-us/library/ms141696.aspx Let me know if you need more help.Please mark as answer if this helps. Thank you. http://thebipalace.wordpress.com

Expanding on the solution of SaeedB: don't just select a table or view in the Lookup component (step 5). Write a SQL query that selects only the columns that you need to check if a row exists or not. In other words, only get the natural key of the table. That way, only the data that you need will be pulled into the SSIS package and thus it will save you memory. And enhance performane. If you need all the columns, still write a query, as it can perform slightly better behind the scenes. Also check out the various caching options of the lookup component. If the lookup table isn't too big, you can choose the Full Cache options, which will drastiscally improve performance. If the lookup table is large, you can consider a partial cache or using a cache transform to "warm up" the cache.MCTS, MCITP - Please mark posts as answered where appropriate.