x

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

palum gravatar image

palum
249 25 29 30

(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

alexsdba gravatar image

alexsdba
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

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x927
x279
x67
x9

asked: Jan 13, 2012 at 09:19 AM

Seen: 1929 times

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