excel source not reading newly/appended rows

I am trying to ETL from excel source to SQL table. When I created the package my excel had certain number of rows. Later if I add (append) few more rows in the spreadsheet ... the ssis package (also in excel source preview) not reading the newly added rows. Suprisingly it is reading new rows if they are inserted in between existing rows.

Any idea why ?

Just a random thought, but are there possibly any hidden rows between the original data and your appended data? Check to make sure the row numbers are contiguous.
|||

JayH wrote:

Just a random thought, but are there possibly any hidden rows between the original data and your appended data? Check to make sure the row numbers are contiguous.

No hidden rows... I m experiencing this with couple of packages having excel as source.

|||Does it work if you delete and recreate the Excel source and connection manager after adding the rows to the end?|||

Ted Lee - MSFT wrote:

Does it work if you delete and recreate the Excel source and connection manager after adding the rows to the end?

Yes it works if I do that...

|||

My problem is solved. Under name of the excel sheet in excel source editor two items appear one with original name of the excel file and another one with $ sign appended to excel file name. If I select the one with $ sign it worked for me.

Any idea what both items mean?

Thanks

|||

See the following in Books Online for the Excel Source:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10is_0evalplan/html/e66349f3-b1b8-4763-89b7-7803541a4d62.htm

"In Excel, a worksheet or range is the equivalent of a table or view. The list of available tables in the Excel Source and Destination editors displays existing worksheets (identified by the $ sign appended to the worksheet name, such as Sheet1$) and named ranges (identified by the absence of the $ sign, such as MyRange). For more information, see the Usage Considerations section."

|||

Thanks Ted...