Script Task: There is already an open DataReader associated with this Command which must be closed first.'

Question

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
//added below name spaces
using System.IO;
using System.Data.OleDb;
using System.Data.SqlClient;
#endregion

namespace ST_1e421b7ecb5c48fbaa68ec2e67df7ec0
{

        public void Main()
        {
            {
                string FilePath = Dts.Variables["User::FilePath"].Value.ToString();
                string TableName = Dts.Variables["User::TableName"].Value.ToString();
                string SchemaName = Dts.Variables["User::SchemaName"].Value.ToString();
                //User::FilePath,User::SchemaName,User::TableName,User::Variable

                //var directory = new DirectoryInfo(FilePath);
                //FileInfo[] files = directory.GetFiles();

                //Declare and initilize variables
                string fileFullPath = "";
                SqlConnection myADONETConnection = new SqlConnection();
                myADONETConnection = (SqlConnection)(Dts.Connections["DBConn1"].AcquireConnection(Dts.Transaction) as SqlConnection);

                //Get one Book(Excel file at a time)
                //foreach (FileInfo file in files)
                //{
                    fileFullPath = FilePath ;

                    string filename = "";
                    filename = FilePath;

                    //Create Excel Connection
                    string ConStr;
                    string HDR;
                    HDR = "YES";
                    ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
                    OleDbConnection cnn = new OleDbConnection(ConStr);

                    //Get Sheet Name
                    cnn.Open();
                    DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                    string sheetname;
                    sheetname = "";
                    //Only read data from provided SheetNumber

                    foreach (DataRow drSheet in dtSheet.Rows)
                    {

                        if (drSheet["TABLE_NAME"].ToString().Contains("$"))
                        {
                            sheetname = drSheet["TABLE_NAME"].ToString();
                        //MessageBox.Show(sheetname);
                        //myADONETConnection.Open();
                        SqlCommand commandSourceData = new SqlCommand ("Select FilePath, SheetName from "+schemaname+"."+"Tablename", myADONETConnection);
                        SqlDataReader reader = commandSourceData.ExecuteReader();

                        //Load Data from DataTable to SQL Server Table.
                        using (SqlBulkCopy BC = new SqlBulkCopy(myADONETConnection))
                            {
                                BC.DestinationTableName = SchemaName + "." + TableName;
                            SqlBulkCopyColumnMapping mapID =
                            new SqlBulkCopyColumnMapping("FilePath", FilePath);
                            BC.ColumnMappings.Add(mapID);

                            SqlBulkCopyColumnMapping mapName =
                                new SqlBulkCopyColumnMapping("SheetName", sheetname);
                            BC.ColumnMappings.Add(mapName);

                            /*foreach (var column in dt1.Columns)
                                    BC.ColumnMappings.Add(column.ToString(), column.ToString());
                            */
                            BC.WriteToServer(reader);
                            reader.Close();

                            }

                        }


                    }
            }
        }
        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

Answer 1

@Sue_8827 , based on my test, I reproduced your problem. You could try to add code MultipleActiveResultSets=True; to the sqlerver connectionstring if you just want to solve the problem you mentioned.

According to your code, I find that you are getting the sheetname and the filepath from the excel. I prefer that you use the insert statement to add the data from the datatable to the database instead of using SqlBulkCopy to do it. Because the filepath is not coming from the datatable.

Here is a code example you could refer to.

static void Main(string[] args)  
    {  
        string FilePath = "D:\\Example.xlsx";  
  
        string TableName = "Example";  
        string SchemaName = "dbo";  
        //Declare and initilize variables  
        string fileFullPath = "";  
        SqlConnection myADONETConnection = new SqlConnection("Connstr; MultipleActiveResultSets=True;");  
        myADONETConnection.Open();  
        fileFullPath = FilePath;  
  
        string filename = "D:\\Example.xlsx";  
        filename = FilePath;  
  
        //Create Excel Connection  
        string ConStr;  
        string HDR;  
        HDR = "YES";  
        ConStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileFullPath + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";  
        OleDbConnection cnn = new OleDbConnection(ConStr);  
  
        //Get Sheet Name  
        cnn.Open();  
        DataTable dtSheet = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
        string sheetname;  
        sheetname = "";  
        //Only read data from provided SheetNumber  
  
        foreach (DataRow drSheet in dtSheet.Rows)  
        {  
  
            if (drSheet["TABLE_NAME"].ToString().Contains("$"))  
            {  
                sheetname = drSheet["TABLE_NAME"].ToString();  
                string sql = string.Format("insert into {0} (FilePath,SheetName) values(@FilePath,@SheetName)", SchemaName + "." + TableName);  
                SqlCommand cmd = new SqlCommand(sql, myADONETConnection);  
                cmd.Parameters.AddWithValue("@FilePath", FilePath);  
                cmd.Parameters.AddWithValue("@SheetName", sheetname);  
  
                cmd.ExecuteNonQuery();  
                 
  
            }  
        }  
    }  

Result:

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.