Importing from Excel sheet with unknown name
 

I receive an Excel file with a lot of data every day, and I have build
a simple import SSIS package which also takes care of som
transformations etc.
The people who send me the file apparently use different setup on
their computers, meaning that some use Englsh MS Excel, and others use
Danish MS Excel. This results in the problem, that sometimes the first
worksheet is named Sheet1, and sometimes it is named Ark1 (Danish for
Sheet1).

The Excel Source in the Dataflow task has to have a name of the sheet,
so every once in a while the job fails, and I have to open the Excel
file and rename first sheet.

Is there any way that I can read the sheetname, or maybe use an "OR"
statement, or maybe rename the sheetname at first?

Best regards.

Soren.

Btw.: I have build the package to loop through all files, if more than
one file is sent. Therefore my Excel Connection Manager is set up with
a connectionstring= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
@[User::Filnavn] + ";Extended Properties=\"Excel 8.0;HDR=Yes\""

Importing from Excel sheet with unknown name
 

On 30 Jan., 09:13, Soren wrote:
I receive an Excel file with a lot of data every day, and I have build
a simple import SSIS package which also takes care of som
transformations etc.
The people who send me the file apparently use different setup on
their computers, meaning that some use Englsh MS Excel, and others use
Danish MS Excel. This results in the problem, that sometimes the first
worksheet is named Sheet1, and sometimes it is named Ark1 (Danish for
Sheet1).

The Excel Source in the Dataflow task has to have a name of the sheet,
so every once in a while the job fails, and I have to open the Excel
file and rename first sheet.

Is there any way that I can read the sheetname, or maybe use an "OR"
statement, or maybe rename the sheetname at first?

Best regards.

Soren.

Btw.: I have build the package to loop through all files, if more than
one file is sent. Therefore my Excel Connection Manager is set up with
a connectionstring= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
@[User::Filnavn] + ";Extended Properties=\"Excel 8.0;HDR=Yes\""

Sorry.. Wrong group...