CODEWITHSUNDEEP
sql-serverexcelssissql-server-2008-r2ssis-2008
Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

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

Upvotes: 1

Views: 4740

Answers (2)

Hadi
Hadi

Reputation: 37313

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:

Upvotes: 2

ASH
ASH

Reputation: 20322

You may want to consider doing an UPSERT (Update existing/Insert new). Matches are inserted, and non-matches are redirected elsewhere.

http://rad.pasfu.com/index.php?/archives/46-SSIS-Upsert-With-Lookup-Transform.html

Upvotes: 0

Related Questions