SSIS sheetname variables where spaces in sheet names - loading to SQL.

See more:

MS-Excel

SSIS

Hi,

I am following an example at the link below:

How to load data from multiple Excel sheets to any destination in SSIS[^]


I have not used Microsoft Jet 4.0 OLE DB Provider as i get a no stable ISAM message. So have used ADO.NET:OLEDB (MS office 12.0 Access Database Engine OLE DB Provider
Extended properties: Excel 12.0 Macro;HDR=YES)

The problem i am having is that with my workbook the worksheet names variable is not updating?

The for each loop has picked up on there being six sheets.

The first sheet being:

Med fit calc Jan 16

The second
Med fit calc Feb 16

etc.

However, the package which loads to a SQL table, loads the first sheet 6 times instead of 6 sheets, one time each. So i am guessing the variable (varSheetName) is not being updated.

I have initialised varSheetName as string with value
Med fit calc Jan 16$

I have also tried renaming sheets to Jan16 etc and the variable as Jan16$

Can anyone please advise?

What I have tried:

I have tried changing sheet names and Providers.