Import data from Excel using SSIS without knowing sheet name

Question

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.

Solution 1

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 */

Solution 2

Just for the record, I'm using this code in Script Task to solve the problem. Variables used are: Filename, SheetName.

Note that my Excel filename is dynamic.

// GET NAME OF FIRST SHEET
string filename = (string)Dts.Variables["Filename"].Value;
string sheetName = null;

string connStr =
    String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"EXCEL 8.0;IMEX=1;\"", filename);

var conn = new OleDbConnection(connStr);
try 
{           
    conn.Open();

    using(var dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
    {
        var row0 = dtSheet.Rows[0];
        sheetName = row0["TABLE_NAME"].ToString();
    }
}
catch (Exception)
{
    throw;
}
finally
{
    conn.Close();
    conn.Dispose();
}

if (!String.IsNullOrEmpty(sheetName))
{
    Dts.Variables["SheetName"].Value = sheetName;
    Dts.Events.FireInformation(1, "User::SheetName", sheetName, "", 0, ref dummy);
    Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
    Dts.Events.FireError(0, "User::SheetName", "No SheetName found!", String.Empty, 0);
    Dts.TaskResult = (int)ScriptResults.Failure;
}

Solution 3

I had a similar problem. The solution that I implemented was first read the excel file using OleDB connection. Open the connection and then retrieve all the sheet names. Here is an example

Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ABC.xls;Extended Properties=""EXCEL 8.0;"""

Dim lstSheetName As List(Of String) = Nothing
Try
 objConn = New OleDbConnection(Me.ConnectionString)
 objConn.Open()
 lstSheetName = New List(Of String)
 Using dtSheetTable As DataTable =       objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,Nothing)

  For Each drRow As DataRow In dtSheetTable.Rows
     lstSheetName.Add("[" & drRow("TABLE_NAME").ToString().Replace("'", "''") & "]")
  Next
 End Using
Catch ex as Exception
 Throw
Finally
 If objConn.State = ConnectionState.Open Then objConn.Close()
 objConn.Dispose()
End Try

This all code is written ASPX.VB and then I am executing the SSIS package through code behind and passing the first value in the lstSheetName variable (lstSheetName(0).ToString())

This was

Solution 4

If anyone has trouble with the JET Driver you can use the AccessDatabase drivers now. This was adapted from above and is verified working on my machine, no extra references are needed for this.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Data.OleDb;

    public void Main()
    {
        // GET NAME OF FIRST SHEET
        string filename = Dts.Variables["User::ActiveFileName"].Value.ToString();
        string sheetName = null;
        bool dummy = true;

        string connStr =
            String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"EXCEL 12.0 XML;HDR=YES\";", filename);
        var conn = new OleDbConnection(connStr);
        try
        {
            conn.Open();

            using(var dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
            {
                var row0 = dtSheet.Rows[0];
                sheetName = row0["TABLE_NAME"].ToString();
            }

            if (!String.IsNullOrEmpty(sheetName))
            {
                Dts.Variables["SheetName"].Value = sheetName;
                Dts.Events.FireInformation(1, "User::SheetName", sheetName, "", 0, ref dummy);
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            else
            {
                throw new Exception("No SheetName found!");
            }
        }
        catch (Exception ex)
        {
            Dts.Events.FireError(0, "User::SheetName", ex.Message, String.Empty, 0);
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
    }

Solution 5

I don't think so...I don't know of any ordinal reference syntax, e.g., Sheets[0] that you could use.

So if you can't get the data without knowing the sheet name - you just need to dynamically find out the sheet name. This link on getting Excel schema info in SSIS should help you do that. Once you have that, you can pass the sheet name in as a variable, and away you go.

View more solutions

Comments

[Ken Pespisa]

This is the best option in my opinion, and if I had to approach this problem again I would try this route first. Thanks AdamA! The project I was working already used UI automation, though, so I ended up just appending a script to automate change the name.

[PeterX]

Update: I found my Excel reference here: C:\Program Files (x86)\Microsoft Visual Studio 11.0\Visual Studio Tools for Office\PIA\Office14\Microsoft.Office.Interop.Excel.dll. How do I get a reference to "Excel.Application"? Do I need to a add a DLL reference? I'm running 64-bit Excel with the "Microsoft Access Database Engine 2010 Redistributable" installed to allow me to use Excel source in SSIS.

[PeterX]

There also appears to be a stray "xlWorkBook" value in the Open method in your code above.