Use ssis to load multiple files with multiple sheets

Question

Use ssis to load multiple files with multiple sheets

debbie.coates 10909

SSC Veteran

Points: 253
More actions
January 22, 2019 at 2:58 am

#379110

Hello
Has anyone ever loaded multiple excel files each with multiple sheets into excel
I've got a package that I've managed to create (with the help of the internet) that loop round and loads each file file in, but only Sheet1, what I am also trying oto do is loop round the sheets int workbook.
I have file one, two and three, and each file has Sheet a,b,c.. So its like a loop inside a loop
Has anyone done this, if so, could you point me in the right direction please. I have been trawling the net, and I can fine either loop over files or loop round sheets, but not both.
Much appreciated
Debbie

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

HandyD Mr or Mrs. 500 Points: 507 More actions · Answer 1

If you have the package that can loop over sheets you should be able to put that inside a For Loop container that iterates over the files. Basically configure it as a loop within a loop. This article has great info if you need help iterating over worksheets within a file.

A high level design could be:

For Each File loop to iterate over the files
For Each loop within the File loop that iterates over the worksheets of the current file

aaron.reese SSChampion Points: 13498 More actions · Answer 2

I think you will be OK with the suggestion above as long as the sheets follow a naming convention or you can iterate through the sheet names from another data source AND the data structure on each sheet is the same. it wont like it if the sheets are called
Sales NE, Sales SW, Sales Central, Configuration page

Generally I have found that SSIS and Excel do not play nicely together. There are all sorts of issues with users renaming sheets and columns, changing data types and not adhering to agreed conventions (e.g. Column Due_Date you would expect a blank or a date but users tend to do things like 'TBA' which completely confuses the SSIS data pipeline). I have also had problems with merged fields and protected data. If at all possible I would push to get the data from the original source system that generated the spreadsheets, or try to get the data in a different format (CSV). It could save you a whole bunch of headaches down the line.

Aaron

HandyD Mr or Mrs. 500 Points: 507 More actions · Answer 3

aaron.reese - Tuesday, January 22, 2019 10:05 AM
I think you will be OK with the suggestion above as long as the sheets follow a naming convention or you can iterate through the sheet names from another data source AND the data structure on each sheet is the same. it wont like it if the sheets are called
Sales NE, Sales SW, Sales Central, Configuration page

Agree Aaron. In the past I've had to use a script task and the Interop Assemblies to iterate over the sheets in a workbook and get the sheet names for processing when the names vary. You can also use a script task to dynamically set source and destination column metadata before moving data. Its complicated but it works well enough once you've got it in place.