question

palum avatar image
palum asked

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.
ssistsqlscripttask
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

alexsdba avatar image
alexsdba answered
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$]')
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered. Doesnt works out in my case :(
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.