I am sure there is a package some where which does this function. Could any one please point me in the right direction? Basically I have a live table and test table. the test table needs to be populated with the updated live data everyday. I could easily right a query by comparing each column from these two tables, but my manger wanted me to write an SSIS package for this because it would be more efficient then hard coding each column, since each table has around 50 columns. Did any person wrote this kind of package before. i know there are tools like red gate or tabledifference, but i cannot use them in office. Please advise. thanks

I can simply offer you a very through post by Jamie Thomson to read that contains two methods: http://www.sqlis.com/sqlis/post/Get-all-from-Table-A-that-isnt-in-Table-B.aspx but I think only method 1 is applicable in your case and it will require some fine tuning to adopt to your needs.Arthur My Blog

i know there are tools like red gate or tabledifference, but i cannot use them in office. Please advise. DBA-s and database developers do rely on redgate tools for schema and/or data compare. Why do you have to reinvent the wheel? Possibly with bugs? Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 Grand Slam

Hope the below transformation task helps http://cozyroc.com/ssis/table-difference Also if you want to develop a new SSIS package , You can do with Merge Join trasnformation task http://msdn.microsoft.com/en-us/library/ms141775.aspx