Linked Excel files in MS SQL server management studio

Question

I am working on MS SQL server management studio 2014. Using linked Server, I can link Excel File as a Linked Server. I know how to link. But I want to know how can I link 5 files of Excel on a Linked Server or I have to create separate Linked server of each excel files?

Thanks in advance,

Answer 1

You would setup it using either OLEDB provider or the provider for ODBC drivers and create a connection using the ODBC Administrator tool on the server to the Excel file.

If you are suppose to read from Excel file on a regular basis then this method is very useful.

Also, you can use SQL Server Management Objects SMO to configure Excel data source as Linked Server, using VBA code.

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim s As Server
        Dim conn As ServerConnection
        Dim ls As LinkedServer

        conn = New ServerConnection("ServerName\InstanceName", "YourUesrName", "YourPassword")
        s = New Server(conn)
        Try
            ls = New LinkedServer(s, "XLTEST_DMO")
            With ls
                .ProviderName = "Microsoft.Jet.OLEDB.4.0"
                .ProductName = "Excel"
                .DataSource = "c:\book1.xlsx"
                .ProviderString = "Excel 9.0"
            End With
            ls.Create()
            MessageBox.Show("New link has been created.")
        Catch ex As SmoException
            MessageBox.Show(ex.Message)
        Finally
            ls = Nothing
            If s.ConnectionContext.IsOpen = True Then
                s.ConnectionContext.Disconnect()
            End If
        End Try

    End Sub
End Class

I hope this help you.