locked
Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.The wait operation timed out RRS feed

  • Question

  • Hi,

     I am trying to split data from a table which has 120 million rows into multiple .txt files with 1 million records in each file.

    I am using script task in SSIS to achieve the same but after executing the package, it is creating around 8 txt files and then failing with below error.

    "SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out"

    Below is the code from the scrip task :

    #region Help:  Introduction to the script task
    /* The Script Task allows you to perform virtually any operation that can be accomplished in
     * a .Net application within the context of an Integration Services control flow. 
     * 
     * Expand the other regions which have "Help" prefixes for examples of specific ways to use
     * Integration Services features within this script task. */
    #endregion


    #region Namespaces
    using System;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using System.IO;
    using System.Data.SqlClient;
    #endregion

    namespace ST_056616145e7743499d097317fe432711
    {
        /// <summary>
        /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
        /// or parent of this class.
        /// </summary>
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
        public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
        {
            #region Help:  Using Integration Services variables and parameters in a script
            /* To use a variable in this script, first ensure that the variable has been added to 
             * either the list contained in the ReadOnlyVariables property or the list contained in 
             * the ReadWriteVariables property of this script task, according to whether or not your
             * code needs to write to the variable.  To add the variable, save this script, close this instance of
             * Visual Studio, and update the ReadOnlyVariables and 
             * ReadWriteVariables properties in the Script Transformation Editor window.
             * To use a parameter in this script, follow the same steps. Parameters are always read-only.
             * 
             * Example of reading from a variable:
             *  DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
             * 
             * Example of writing to a variable:
             *  Dts.Variables["User::myStringVariable"].Value = "new value";
             * 
             * Example of reading from a package parameter:
             *  int batchId = (int) Dts.Variables["$Package::batchId"].Value;
             *  
             * Example of reading from a project parameter:
             *  int batchId = (int) Dts.Variables["$Project::batchId"].Value;
             * 
             * Example of reading from a sensitive project parameter:
             *  int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
             * */

            #endregion

            #region Help:  Firing Integration Services events from a script
            /* This script task can fire events for logging purposes.
             * 
             * Example of firing an error event:
             *  Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
             * 
             * Example of firing an information event:
             *  Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
             * 
             * Example of firing a warning event:
             *  Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
             * */
            #endregion

            #region Help:  Using Integration Services connection managers in a script
            /* Some types of connection managers can be used in this script task.  See the topic 
             * "Working with Connection Managers Programatically" for details.
             * 
             * Example of using an ADO.Net connection manager:
             *  object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
             *  SqlConnection myADONETConnection = (SqlConnection)rawConnection;
             *  //Use the connection in some code here, then release the connection
             *  Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
             *
             * Example of using a File connection manager
             *  object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
             *  string filePath = (string)rawConnection;
             *  //Use the connection in some code here, then release the connection
             *  Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
             * */
            #endregion


            /// <summary>
            /// This method is called when this script task executes in the control flow.
            /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
            /// To open Help, press F1.
            /// </summary>
            public void Main()
            {
                // TODO: Add your code here

                string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
                try
                {

                    //Declare Variables
                    string FileNamePart = Dts.Variables["User::FileNamePart"].Value.ToString();
                    string DestinationFolder = Dts.Variables["User::DestinationFolder"].Value.ToString();
                    string TableName = Dts.Variables["User::TableName"].Value.ToString();
                    string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
                    string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
                    Int32 RecordCntPerFile = (Int32)Dts.Variables["User::RecordsPerFile"].Value;
                    string RecordCntPerFileDecimal = RecordCntPerFile + ".0";


                    //USE ADO.NET Connection from SSIS Package to get data from table
                    SqlConnection myADONETConnection = new SqlConnection();
                    myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);


                    //Read distinct Group Values for each Excel Sheet
                    string query = "select ceiling(count(*)/" + RecordCntPerFileDecimal + ") AS LoopCnt from " + TableName+ " (nolock)";
                    decimal LoopCnt = 0;

                    //Get the Count of Sheets need to be created
                    SqlCommand cmd = myADONETConnection.CreateCommand();
                    cmd.CommandText = query;
                    cmd.CommandTimeout = 7200;
                    LoopCnt = (decimal)cmd.ExecuteScalar();
                    

                    int startRowCnt = 0;
                    int endRowCnt = RecordCntPerFile;

                    for (int fileloop = 1; fileloop <= LoopCnt; fileloop++)
                    {


                        //Load Data into DataTable from SQL ServerTable
                        string queryString = ";with cte as (Select *, Row_Number() over (order by (Select 1)) AS RowNumber from " +
                        TableName + ") Select * From cte where RowNumber > " + startRowCnt.ToString() + " and RowNumber<=" + endRowCnt.ToString();
                        SqlDataAdapter adapter = new SqlDataAdapter(queryString, myADONETConnection);
                        DataSet ds = new DataSet();
                        adapter.Fill(ds);

                        foreach (DataTable d_table in ds.Tables)
                        {
                            string FileFullPath = DestinationFolder + "\\" + FileNamePart + "_" + fileloop.ToString() + FileExtension;

                            StreamWriter sw = null;
                            sw = new StreamWriter(FileFullPath, false);

                            // Write the Header Row to File
                            int ColumnCount = d_table.Columns.Count;
                            for (int ic = 0; ic < ColumnCount; ic++)
                            {
                                sw.Write(d_table.Columns[ic]);
                                if (ic < ColumnCount - 1)
                                {
                                    sw.Write(FileDelimiter);
                                }
                            }
                            sw.Write(sw.NewLine);

                            // Write All Rows to the File
                            foreach (DataRow dr in d_table.Rows)
                            {
                                for (int ir = 0; ir < ColumnCount; ir++)
                                {
                                    if (!Convert.IsDBNull(dr[ir]))
                                    {
                                        sw.Write(dr[ir].ToString());
                                    }
                                    if (ir < ColumnCount - 1)
                                    {
                                        sw.Write(FileDelimiter);
                                    }
                                }
                                sw.Write(sw.NewLine);

                            }

                            sw.Close();

                        }
                        startRowCnt += RecordCntPerFile;
                        endRowCnt += RecordCntPerFile;
                        Dts.TaskResult = (int)ScriptResults.Success;
                    }

                }

                catch (Exception exception)
                {

                    // Create Log File for Errors
                    using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString() + "\\" +
                        "ErrorLog_" + datetime + ".log"))
                    {
                        sw.WriteLine(exception.ToString());
                        Dts.TaskResult = (int)ScriptResults.Failure;


                    }
                }
            }

            #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

        }
    }



    harshac

    Tuesday, July 28, 2020 10:48 PM

Answers

  • Spent about 15 min. trying to find suitable script. This is a sample of the idea which you may expand upon:

    et nocount on;
    declare @PageSize int = 1000000, @PageNumber int = 0;
    declare @iterations tinyint = 0;
    
    declare @RowsAffected int = 1;
    while @RowsAffected >0
    begin
    set @iterations = @iterations + 1;
    RAISERROR ('Iteration # %d', 0, 1, @iterations) WITH NOWAIT;
    
    -- here you may want to use sqlcmd mode to output to a file - may be through xp_cmdshell procedure to generate file name dynamically
    
    SELECT 
          [FileName]
    	  ,[RecordID]
    	  ,1
          other columns here
      FROM [source].[CallLogs]
    
    ORDER BY RecordId
    OFFSET @PageSize * @PageNumber ROWS
    FETCH NEXT @PageSize ROWS ONLY;
    
    
    set @RowsAffected = @@ROWCOUNT;
    set @PageNumber = @PageNumber + 1;
    
    end


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Harshac Tuesday, August 11, 2020 2:20 PM
    Wednesday, July 29, 2020 1:45 PM

All replies

  • this code looks like it does a file IO for each column of each row. For 1 million rows per step, unknown number of columns, thats alot of system IO calls.

    I would use string builder, preallocated 1-10 M. make a good guess as to the average bytes per row (ballpark). then estimate the number of rows to fill your string buffer allocation (or just less than it)

    or just start populating the string builder, when its length/size is some what less than allocation size, do the Write IO, flush the string builder, and repeat until done.

    I might also skip the SqlDataAdapter and Table, how big is 1M rows?

    C# / .NET does not work well when memory allocation is over 1GB, so use DataReader, and look into the sequential access options 


    jchang

    Tuesday, July 28, 2020 11:45 PM
  • Why not do the whole process in 120 loops of just selecting 1mln rows and output them into text file directly from one stored procedure using sqlcmd to generate the file? I think it should be quick enough.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, July 29, 2020 12:08 AM
  • Hi Harshac,

    //Get the Count of Sheets need to be created
                    SqlCommand cmd = myADONETConnection.CreateCommand();
                    cmd.CommandText = query;
                    cmd.CommandTimeout = 7200;
                    LoopCnt = (decimal)cmd.ExecuteScalar();

    Please try to set the value of CommandTimeout much bigger.

    We can refer to SqlCommand.CommandTimeout Property.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 29, 2020 8:41 AM
  • Can you please elaborate me? do you have a sample code?

    harshac

    Wednesday, July 29, 2020 1:18 PM
  • cmd.CommandTimeout = 7200;

    Please try to set the value of CommandTimeout much bigger.

    It is already on 7200 sec = 120 min = 2 hours.

    One could set it to 0 for non timeout at all.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 29, 2020 1:35 PM
  • Spent about 15 min. trying to find suitable script. This is a sample of the idea which you may expand upon:

    et nocount on;
    declare @PageSize int = 1000000, @PageNumber int = 0;
    declare @iterations tinyint = 0;
    
    declare @RowsAffected int = 1;
    while @RowsAffected >0
    begin
    set @iterations = @iterations + 1;
    RAISERROR ('Iteration # %d', 0, 1, @iterations) WITH NOWAIT;
    
    -- here you may want to use sqlcmd mode to output to a file - may be through xp_cmdshell procedure to generate file name dynamically
    
    SELECT 
          [FileName]
    	  ,[RecordID]
    	  ,1
          other columns here
      FROM [source].[CallLogs]
    
    ORDER BY RecordId
    OFFSET @PageSize * @PageNumber ROWS
    FETCH NEXT @PageSize ROWS ONLY;
    
    
    set @RowsAffected = @@ROWCOUNT;
    set @PageNumber = @PageNumber + 1;
    
    end


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by Harshac Tuesday, August 11, 2020 2:20 PM
    Wednesday, July 29, 2020 1:45 PM
  • Thanks much for your time.

    harshac

    Tuesday, August 11, 2020 2:21 PM