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: 247037 More actions · Answer 1

What problems are you having?

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

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: 247037 More actions · Answer 3

Phil Parkin

SSC Guru

Points: 247037

August 30, 2010 at 7:50 am

#1213725

Please post the full text of the error message.

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: 247037 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?

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: 247037 More actions · Answer 7

Phil Parkin

SSC Guru

Points: 247037

August 30, 2010 at 8:20 am

#1213751

Great - and the answer to the first question?

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: 247037 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.

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