Hi I need to load the Reporting schema Tables every day from Staging tables Using SSIS.What is best way to load from staging.. Lets say example Staging.loan this will truncate every day and load the data from other Source. Now i need to load the Report.loan every day from Staging.loan with out truncating the Report.loan . I need to Update the old records and Insert the New records...as some Records in Source will update every day like Amounts...

If the staging table is located on the same local server, then just use an execute SQL task. In that task you'll write the appropriate insert into Report.loan statement from the Staging.Loan table. About updating records: If you are truncating and re-loading data into Staging.Loan, I'm not sure why you would need to update amounts unless you have some additional parameters that you did not disclose. Please 'Mark as Answer' if found helpful - Chris@tier-1-support

If the staging table is located on the same local server, then just use an execute SQL task. In that task you'll write the appropriate insert into Report.loan statement from the Staging.Loan table. About updating records: If you are truncating and re-loading data into Staging.Loan, I'm not sure why you would need to update amounts unless you have some additional parameters that you did not disclose. Please 'Mark as Answer' if found helpful - Chris@tier-1-support

the issue is similar to Slowly changing dimension problem. Hence, you can apply any of solutions applied to solve the slowly changing dimension problem. Have a look at alternative solutions to slowly changing dimension from the following blog. http://blogs.msdn.com/b/mattm/archive/2009/11/13/handling-slowly-changing-dimensions-in-ssis.aspx If the staging and the reporting db's are on the same server, then try to use the option with the merge statement, given that you have SQL Server 2008 or newer version. hope that helps, Sedat