SSIS- Script task to validate the Excel Source

I have a excel data source which has two coloumns (flg,div). I want to import data from excel data source, before importing i want to validate the coloumns. I have only one condition to validate the excel source. 1. It should have a DISTINCT div.

 flg   div
 1     sec a
 0     sec b
 1     sec c
 1     sec d
 1     sec a

Suppose in the div coloumn there are two sec a, Instead of inserting 2 rows i want to insert only single row of sec a. Somthing like a DISTINCT functionality

I guess we can achieve by using a script component to validate the excel sheet before loading. Can please anyone help me with this??

Or is there any other way ..please recommend.

more ▼

asked Jan 13, 2012 at 09:19 AM in Default

avatar image

249 27 29 33

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You can use Open DataSource or OpenRowset

SELECT DISTINCT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT DISTINCT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT DISTINCT INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT FROM [Customers$]')

more ▼

answered Jan 13, 2012 at 11:49 AM

avatar image

221 1 1 4

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

Doesnt works out in my case :(

Jan 13, 2012 at 03:47 PM palum
(comments are locked)
10|1200 characters needed characters left

If you want to insert distinct values, then validating the excel for distinct values is wasting of processing power if you really want to inset them.

For that purposes you can use a Aggregate Transformation to group the input data to receive distinct values and then insert them.

There is also a possibility to use a Lookup Transformation with partial caching for detection whether particular data were already inserted or not and in case of duplicity simply ignore the duplicate rows.

In case you want only validate whether there are some duplicities, again you can use Aggregate Transformation, as you can calculate counts and distinct counts and then compare those values to detect whether there are duplicities or not.

more ▼

answered Jan 16, 2012 at 12:46 PM

avatar image

Pavel Pawlowski
22.7k 10 15 26

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 13, 2012 at 09:19 AM

Seen: 2604 times

Last Updated: Jan 13, 2012 at 09:19 AM

Copyright 2016 Redgate Software. Privacy Policy