Search code examples
sql-serverexcelssissql-server-2008-r2ssis-2008

SSIS - Compare Excel With Database

I have a SQL Server 2008 R2 Database In which I've several tables. I'll be getting new data as well as data updates to these tables on daily or weekly basis in excel file format. I'm using SSIS to load the excel files to a staging table and then compare this staging table records with the Master table to find the changes ( because I need to do some audits before updating the changes)

Is there any way in SSIS that I can compare the excel file records directly with the Master table? ie without storing them to a staging table

Solution

  • Using T-SQL

    You can use OPENROWSET() function in a query to read data from excel and you can join it with SQL table without the need of staging tables.

    Query example

    SELECT * FROM AmenityData As a
INNER JOIN  TypesToGroups As b
ON a.ClassCode =  b.FacilityTypeID
INNER JOIN Groups As c
ON b.GroupID = c.GroupID
INNER JOIN  OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\postaldistricts.xls;HDR=YES', 'SELECT *    FROM [Sheet1$]') As d
ON d.[PostalDistricts] = a.[PostalDistrict]

    You can refer to the following links for more informations:

    Note: there are other methods like OPENDATASOURCE() or OPENQUERY() or create a linked server, but the one i tried is OPENROWSET(), you can find more inforrmation about these methods in the links i provided

    Using SSIS

    Using Merge Join

    In SSIS you can create use Merge Join to perform LEFT JOIN and INNER JOIN operations.

    For more informations, refer to my answer at How Intersect and Except result in SSIS

    Using Lookup Transformation

    Or you can use Lookup transformations to perform joins (Merge join has better performance on JOIN), just refer to the following Link: