Import data from multiple excel worksheets through SSIS

Question

Post reply

Import data from multiple excel worksheets through SSIS

PSB

SSChampion

Points: 13195
More actions
August 30, 2010 at 7:33 am

#148555

Hi,
I want to import data from multiple excel worksheets through SSIS to a SQL Server table. Any help appreciated .
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

What problems are you having?

Is the format of all the worksheets the same (column order, datatype etc)?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

PSB SSChampion Points: 13195 More actions · Answer 2

I am trying to reuse the ssis package from this link :

http://biresort.net/blogs/pedrocgd/archive/2009/08/06/stepbystep-ssis-extract-data-from-multiple-excel-worksheets.aspx

But if I use a different excel file name or change the variable value from '01$' to 'Sheet1$' it fails .

Phil Parkin SSC Guru Points: 246993 More actions · Answer 3

Please post the full text of the error message.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

PSB SSChampion Points: 13195 More actions · Answer 4

Below is the error message :

Error at DFT Extract Data From Excel [EX_SRC Get Each WorkSheet Data [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.

Error at DFT Extract Data From Excel [EX_SRC Get Each WorkSheet Data [1]]: Opening a rowset for "'Sheet1$'" failed. Check that the object exists in the database.

------------------------------

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)

Phil Parkin SSC Guru Points: 246993 More actions · Answer 5

Please excuse the obvious question, but is there a worksheet called Sheet1 in the workbook being accessed?

What version of Excel are you using?

Are you working in a 32- or 64-bit environment?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

PSB SSChampion Points: 13195 More actions · Answer 6

PSB

SSChampion

Points: 13195

August 30, 2010 at 8:16 am

#1213748

Excel Version 2003 . 32 bit .

Phil Parkin SSC Guru Points: 246993 More actions · Answer 7

Great - and the answer to the first question?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

PSB SSChampion Points: 13195 More actions · Answer 8

By default when I open an execl sheet , the worksheet names that is seen is sheet1,sheet2 and sheet3 .

Phil Parkin SSC Guru Points: 246993 More actions · Answer 9

Yeah I know that - but what's important is the name of the sheets in the specific Excel sheet that you are accessing in your package.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

PSB SSChampion Points: 13195 More actions · Answer 10

I am trying to use the sheets Sheet1,Sheet2 and Sheet3 from the package instead of 01,02 and 03 sheet.

PSB SSChampion Points: 13195 More actions · Answer 11

I changed the original sheet names in the package to the ones shown below for testing.

01 --> Sheet1

02 --> Sheet2

03 --> Sheet3

PSB SSChampion Points: 13195 More actions · Answer 12

I changed the original sheet names in the package to the ones shown below for testing.

01 --> Sheet1

02 --> Sheet2

03 --> Sheet3