SSIS Design pattern for Multiple XLSB File sheets to multiple tables

Question

There are xlsxb files and each of these consists of varying number of sheets (consists of upto 9 sheets) .All the sheets are of different structure.The requirement is to laoad a specific named sheet data into corresponding table.The specific named sheet may or may not appear in all the xlsb files.

Example:

1st xlsb file consists of  9 sheets(
sh1,sh2,...,sh9)

2nd xlsb file consists of 6 sheets(sh1,sh5,sh6,sh7,sh9,sh2)
3rd  xlsb file consists of 3 sheets(sh5,sh7,sh9)


The idea is all sh9 to be colleted in on table called Table_sh9.All sh5 has to 
be in Table_sh5

What SSIS design pattern can be followed for this.

Possible duplicate of [SSIS Looping through Excel Sheets](http://stackoverflow.com/questions/17572766/ssis-looping-through-excel-sheets) — Tab Alleman, Jul 19 '16 at 13:59
What part of your question is not answered by the solution to the other question? — Tab Alleman, Jul 19 '16 at 14:17
I mean some of the sheets have 8 columns .some othes got 10 columns etc — user1254579, Jul 19 '16 at 15:15
Yes, so each possible sheet will need its own dataflow, and which dataflow you use depends on which sheet you are working on in the loop. — Tab Alleman, Jul 19 '16 at 15:17

score 1 · Answer 1 · answered Jul 19 '16 at 17:02

1

To have this dynamic I recommend you do this with a script task. You can look into OpenXML and ClosedXML to read the data.

https://closedxml.codeplex.com/

I would read the header to determine what table you are loading to, then create a datareader from the input and feed that datareader to sqlbulkcopy.

I have a similar solution that automatically create the tables for me and I use the datastreams library -> https://www.csvreader.com/

answered Jul 19 '16 at 17:02

Joe C

3,925
2
11
31

Thanks for the answer.The thing is as some of the xlsb files does not have some of the mentioned sheet names .package fails ,shows the error "opening a row set for "sheet6" failed" – user1254579 Jul 20 '16 at 09:11

SSIS Design pattern for Multiple XLSB File sheets to multiple tables

1 Answers1