Search code examples
sql-serverexcelssis-2012

How to handle this scenario in single SSIS package?

I'm receiving around 100 excel files on daily basis ,in these 100 files there are 4 types of files which name start with (ALC,PLC,GLC and SLC) and then some random No. and each excel file sheetname is same as filename.

Now inside of each type and each file at cell A3 there is 'request by' and then user name for eg-Request by 'Ajeet' and we want to pick the file which is requested by only 'Ajeet', first few rows are not formatted, actual data start from.

ALC data start from A33 Cell
PLC data start from A36 Cell
GLC data start from A32 cell
SLC data start from A38 cell

And few files having no data so in that case "NoData" is mentioned in respective type of files from where data start.

All type of file containing same no. of column.

So how can we handle all these situation in SSIS and load the data into a single SQL table but without using script task. I have attached snapshot one of the file for your reference.

enter image description here

enter image description here

Solution

  • This will help. how-to-read-data-from-an-excel-file-starting-from-the-nth-row-with-sql-server-integration-services

    Copying the solution here in case the link is unavailable

    Solution 1 - Using the OpenRowset Function

    right click on the Excel Source and select Properties

    modify the OpenRowset property

    Solution 2 - Query Excel Sheet

    Edit the connection manager and change the data access mode to SQL Command

    Solution 3 - Google It

    Google it, The information above is from the first search result