Writing CLR to import data from Excel into SQL Table

• sam.alexander

  Ten Centuries

  Points: 1135

  More actions

  March 17, 2010 at 9:15 am

  #221919

  Hi,

  I'm using VS 2008 to write a CLR that if given the File Path, Worksheet Name, and Table Name it'll import data from Excel into SQL. This is something we use quite often and have many SSIS packages to do, but if we can do it in a CLR it'd be nice to have.

  I have the code written but when it tries to connect to the Excel spreadsheet it gives a permissions problem.

  Here's the code:

  using System;

  using System.Data;

  using System.Data.SqlClient;

  using System.Data.SqlTypes;

  using System.Data.OleDb;

  using System.Data.Common;

  using Microsoft.SqlServer.Server;

  public partial class StoredProcedures

  {

  [Microsoft.SqlServer.Server.SqlProcedure]

  public static void ImportFromFile_XLS(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.Jet.OLEDB.4.0;Data Source=" + FileName + ";Extended Properties='Excel 8.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);

  }

  }

  }

  }

  }

  };

  And in SQL when I run the procedure:

  EXEC dbo.ImportFromFile_XLS 'C:\File.xls', 'TestData', 'impTest'

  It gives me this error:

  Msg 6522, Level 16, State 1, Procedure ImportFromFile_XLS, Line 0

  A .NET Framework error occurred during execution of user-defined routine or aggregate "ImportFromFile_XLS":

  System.Security.SecurityException: Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

  System.Security.SecurityException:

  at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)

  at System.Security.PermissionSet.Demand()

  at System.Data.Common.DbConnectionOptions.DemandPermission()

  at System.Data.OleDb.OleDbConnection.PermissionDemand()

  at System.Data.OleDb.OleDbConnectionFactory.PermissionDemand(DbConnection outerConnection)

  at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

  at System.Data.OleDb.OleDbConnection.Open()

  at StoredProcedures.ImportFromFile_XLS(String FileName, String WorkBook, String TableName)

  .

  The error is being thrown on connection.Open(); when it tries to open the Excel Spreadsheet, but I've given the spreadsheet Full access for Everyone on the SQL Server. I've also tried hard coding the path and worksheet name into the text instead of getting it as a parameter and the same results.

  The database has Trustworthy Set to On, and I have other Procedures in the same CLR that Export data from SQL to CSV files in the same directory so I wouldn't think it's a file system permission.

  Any suggestions? As I said I'm writing this in Visual Studio 2008 and the SQL Server is also SQL Server 2008. Sorry if this is the wrong place to post this since it's in SQL Server 2005, but there's no CLR forum under SQL Server 2008.

  Thanks --

  Sam