Loop through Excel File with multiple worksheets

Question

Hello - quick question -We have a SSIS package that uses multiple excel files as a data source. We ran an into an issue and would like help. We have 2 excel files. We have a  File that has 4 worksheets - OCC, OCC2, ADR, ADR2. We'd like our SSIS package to loop through and load data from worksheets OCC and OCC 2 into a SQL Server table called OCC_DATA, and also then loop through and load data from worksheets ADR and ADR2 into a SQL Server table called ADR_DATA. Our issue right now is that when we run our package, it fails because it tries loading ADR and ADR2 into our OCC table. Is it possible to have SSIS loop through based on a file name and then load the respective data? I.E. - have SSIS look for any worksheets that start or have ADR and then load them into the ADR table. Then, we'd like SSIS to loop through and look for any worksheets that have OCC in its file name and load them into the OCC table. Any help would be appreciated

Answer 1

Hi Vikash Karra,

Are the metadata structure pf all of the worksheet same?

It seems that you can use script task to check the name of worksheet, then load the worksheet to respective table based on the name. Basically using a For each Loop container and drag a script task inside it, then add a Data Flow Task to load data to tables. Please refer to this similar example: Extracting  data from multiple sheets in an Excel file in SSIS

Check if it helps.

Regards,

Pirlo Zhang

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Hello - thanks for the reply Pirlo. We are having issues doing that (as we aren't really familiar with C).

Answer 3

You can implement a logic like below

1. Have a For Each loop with file Enumerator to loop through files

2. Inside the loop have a For Each loop with ADO Schema rowset enumerator to loop through sheets of each file

3. have a variable inside second loop to get sheet name

4. Inside second loop have a dummy SQL task  with query as SELECT 1

5. link the dummy sql task to two data flow tasks- one for OCC table and another for ADR table

6.  Use conditional precedence constraints for both the data flow. Use expression and constraint option, set constraint as OnSuccess and Expression like

FINDSTRING(@SheetName,"ADR_",1) > 0


FINDSTRING(@SheetName,"OCC_",1) > 0
...

7. Inside data flows have excel source to connect to sheets and have OLEDB destination to dump the data to corresponding tables (OCC_DATA for OCC sheets and ADR_DATA for ADR sheets)

8. Make sure Excel connectionstring property is set dynamically based on sheet and file name

see this as a reference for multiple sheet iteration logic

https://www.codeproject.com/Tips/395541/How-to-load-data-from-multiple-Excel-sheets-to-any

---

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page