Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Import data from Excel using SSIS without knowing sheet name

Tags:

sql

excel

ssis

I have a spreadsheet that is updated by another server (out of my control) and I need to automate bringing that data into SQL 2005. The data is always the first page of the spreadsheet. However, the name of that sheet changes depending on the number of rows.

Is there a way to run an SSIS job that pulls in data from Excel without knowing the sheetname beforehand? It seems to rely on the sheet name as the data source, but I'm looking to tell it "sheet number 1" or something similar.

like image 548
Ken Pespisa Avatar asked Dec 14 '10 20:12

Ken Pespisa


People also ask

How do I import Excel data into SQL Server using SSIS?

On the SSIS menu, select New connection. In the Add SSIS Connection Manager dialog box, select EXCEL and then Add. Create the connection manager at the same time that you configure the Excel Source or the Excel Destination on the Connection manager page of the Excel Source Editor or of the Excel Destination Editor.

How do I get data from multiple sheets in Excel using SSIS?

Create an SSIS package for the data import from multiple Excel files. First, we will create an SSIS package for importing a single Excel file data into the SQL Server table. Later, we will convert the same package and import multiple Excel files data using SSIS variables and parameters.


1 Answers

I would script out the Worksheet name to a SSIS User Variable. If you are not opposed to inserting a script task into your SSIS package try this: (Based on link text )

Excel.Application xlApp = new Excel.ApplicationClass();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Open("<Name of your excel app>.xls", 0, xlWorkBook true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
// Look up worksheet by index
Excel.Worksheet xlWorkSheet =(Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

user::worksheetname = xlWorkSheet.Name;

/* Do clean up.  Working with COM object */
like image 86
AdamA Avatar answered Oct 05 '22 21:10

AdamA