Odbc Connection - [ODBC Driver Manager] Data source name not found and no default driver specified

Question

Public Sub Main()
    Dim connSource As New System.Data.Odbc.OdbcConnection

    Dim cmdSource As New System.Data.Odbc.OdbcCommand
    Dim cmdupdate As New System.Data.Odbc.OdbcCommand

    Dim connsql As System.Data.Odbc.OdbcConnection
    Dim cmdsql As System.Data.Odbc.OdbcCommand


    connsql = New System.Data.Odbc.OdbcConnection

    strsqlConnectionString = "Driver={ODBC Driver 11 for SQL Server};" & _
                             "Server=MyDatabaseServer;" & _
                             "DataBase=MyDatabase;" & _
                             "Trusted_Connection=Yes;"

    connsql.ConnectionString = strsqlConnectionString
    connsql.Open()

    cmdSource.Connection = connSource
    cmdupdate.Connection = connsql

    cmdsql = New System.Data.Odbc.OdbcCommand

    Dim m_strSourceExcelFileName As String
    m_strSourceExcelFileName = Dts.Variables("User::FileName").Value.ToString()
    Dim m_strSourceFilePath As String = "D:\Files\2017\"

    m_strConnSource = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + m_strSourceFilePath + "\" + m_strSourceExcelFileName + "; Extended Properties='Excel 12.0 Xml;HDR=NO'"

    connSource.ConnectionString = m_strConnSource
    connSource.Open()

I have the above code snippet in an SSIS script task. The script task contains code to copy read data from excel to SQL Server 2014.

I'm getting the below error at last line 'connSource.Open()' in the snippet above.

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I can't seem to figure out what could be wrong with my connection string. I have AccessDatabaseEngine_x64.exe installed on the machine.

erg erg · Accepted Answer · 2017-03-29T10:48:40

To connect to Excel using ODBC, I use connection strings like:

Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=E:\foo\excelTest.xls;

This works for the driver that comes with ACEODBC.DLL, with the Date 22.03.2010

Odbc Connection - [ODBC Driver Manager] Data source name not found and no default driver specified

1 Answers