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

Sue_8827 81 Reputation points
2021-09-07T03:41:30.137+00:00
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

    }
}
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,495 questions
C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,527 questions
{count} votes

Accepted answer
  1. Jack J Jun 24,476 Reputation points Microsoft Vendor
    2021-09-07T07:44:20.89+00:00

    @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:

    129841-image.png


    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.

    0 comments No comments

0 additional answers

Sort by: Most helpful