question

Sue8827 avatar image
0 Votes"
Sue8827 asked Sue8827 commented

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

 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
    
     }
 }
dotnet-csharpsql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RetainSameconnection is also set to ture.

0 Votes 0 ·

1 Answer

JackJJun-MSFT avatar image
0 Votes"
JackJJun-MSFT answered

@Sue8827, 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.







image.png (9.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.