SSIS - Compare Excel With Database

Question

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

I want to avoid the need of an additional table as well as reduce the db hits — Jayasurya Satheesh, Dec 14 '17 at 05:37
Create temp tables in SSIS and compare them with fact table in database. It is always a good practice to load data into a staging table, as you can retrieve records for any mismatch. — Ven, Dec 14 '17 at 09:36
how do I achieve that ? can you provide some examples @BHouse — Jayasurya Satheesh, Dec 14 '17 at 09:39
@JayasuryaSatheesh you can achieve this using T-SQL, or using merge join or lookup transformation from SSIS, check my answer for more info — Hadi, Jan 01 '18 at 21:10
@JayasuryaSatheesh you can achieve this using [OPENROWSET](https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql) in a SQL statement — Yahfoufi, Jan 22 '18 at 13:15

Hadi · Accepted Answer · 2018-01-01T21:09:54.173

2

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:

Lookup Transformation in SQL Server Integration Services

edited Jan 01 '18 at 21:09

answered Dec 30 '17 at 05:19

Hadi

36,233
13
65
124

I have 2 tables.1 is in Sql server and another is in Oracle and now i want to compare records of this 2 tables based on joining 2 columns and then doing comparsion on 1 or more columns.Then what i need to do in SSIS? – I Love Stackoverflow Feb 06 '18 at 12:28
You can create a linked server for the oracle database from sql server, then use a simple join query – Hadi Feb 06 '18 at 13:13
Or you can use merge join transformation or Lookup to achieve this from SSIS – Hadi Feb 06 '18 at 13:14
If i use Lookup Transformation and if my source and target data are unordered then how does Look up tranformation will work in this case considering source and target have 20 millions of data? – I Love Stackoverflow Feb 06 '18 at 13:46
@Learning-Overthinker-Confused Lookup transformation doesn't require ordering, but Merge Join does. – Hadi Feb 06 '18 at 16:32
@Learning-Overthinker-Confused In your case i don't think there is an optimal solution, you should try and evaluate each method and finally choose the one that fits your requirements – Hadi Feb 06 '18 at 16:32

score 0 · Answer 2 · answered Dec 14 '17 at 21:38

0

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

answered Dec 14 '17 at 21:38

ASH

20,759
19
87
200

I'm not bothered about insert, what I need is a way to Audit the updating records without the use of an intermediate staging table – Jayasurya Satheesh Dec 15 '17 at 04:00

SSIS - Compare Excel With Database

2 Answers2

Using T-SQL

Using SSIS

Using Merge Join

Using Lookup Transformation