.netsql-serversqlclrsqlbulkcopy

Error when importing data from Excel to SQL Server with CLR


SQL DBA here, not a C# or a programmer guy. Below is some code I found online and tried to adapt to my needs:

public partial class StoredProcedures

{

    [Microsoft.SqlServer.Server.SqlProcedure]

    public static void ExcelTransfer(String FileName, String WorkBook, String TableName)

    {

        using (SqlConnection cn = new SqlConnection("context connection = true"))

        {

            cn.Open();

            // Connection String to Excel Workbook

            string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=YES'";

            // Create Connection to Excel Workbook

            using (OleDbConnection connection = new OleDbConnection(excelConnectionString))

            {

                OleDbCommand command = new OleDbCommand("Select Number FROM [" + WorkBook + "$]", connection);
                connection.Open();

                // Create DbDataReader to Data Worksheet

                using (DbDataReader dr = command.ExecuteReader())

                {

                    // Bulk Copy to SQL Server

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cn))

                    {

                        bulkCopy.DestinationTableName = TableName;

                        bulkCopy.WriteToServer(dr);

above code is registered in SQL Server with:

CREATE ASSEMBLY ImportFromFile_XLS
FROM '<path to .dll>'
WITH PERMISSION_SET = UNSAFE
GO

CREATE procedure readExcel (@str1 nvarchar(255), @str2 nvarchar(255), @str3 nvarchar(255))
AS
EXTERNAL NAME ImportFromFile_XLS.StoredProcedures.ExcelTransfer

When I execute

EXEC dbo.readExcel 'C:\SQL_DATA\WorkBook.xls', 'WorkBook', 'testTable'

I get the error message:

Msg 6522, Level 16, State 1, Procedure dbo.readExcel, Line 0 [Batch Start Line 46] A .NET Framework error occurred during execution of user-defined routine or aggregate "readExcel": System.InvalidOperationException: The requested operation is not available on the context connection. System.InvalidOperationException: at System.Data.SqlClient.SqlBulkCopy.CreateOrValidateConnection(String method) at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServerAsync(Int32 columnCount, CancellationToken ctoken) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DbDataReader reader)
at StoredProcedures.ExcelTransfer(String FileName, String WorkBook, String TableName) .

What am I doing wrong here?


Solution

  • As others have mentioned in comments, Context Connection is not valid for using with SqlBulkCopy, as noted in the documentation — Context Connections and Regular Connections - Restrictions (noted by @AlwaysLearning). A standard connection string is required.

    HOWEVER, the documentation and comments are only partially correct. The Context Connection is only invalid for the destination connection. It is, on the other hand, valid for the source connection. That won't help here as the connection in question is the destination, but it should still be documented (and if I get a chance, I will update the Microsoft documentation to be more specific).