SSIS Data Validation and Data Loading

Question

I need suggestion on best approach from below listed options. I need to validate excel file data and load it to SQL Server

Validations include

• Non Duplicate columns
• Mandatoty fields present
• Fields not present in Database

In case of error I would write in errorlog table in database

Below is my approach

• Load the Data into a Temp Table in Database
• Run the Validations
• Log the Error
• On success load it to main tables

Please let me know if you have any other better ideas for this scenario

Answer 1

Here are couple of approaches that are possible:

1. Using SSIS

Create excel connection manager then use dataflow task with OLEDB Source, lookup transform (to eliminate the records NOT needed), OLEDB destination directly into main table.

You can also choose to redirect or ignore rows that do not satisfy the transformations.

(use can use bulk insert task if the excel is really large instead of dealing RBAR)

2. Using TSQL

BULK INSERT or BCP or use OPENROWSET into staging table. Beware that you need to have approriate drivers installed (JET for x32 or ACE for x64 SQL Server).

Then do error handling by logging to error table (raiseerror, try-catch) before loading to main table.