SSIS script task throws out of memory exception on 3mln rows file

Naomi Nosonovsky 8,771 Reputation points
2025-10-07T17:20:03.17+00:00

I have a SSIS package with the following script task to add footer to the file:

#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.Text;
#endregion

namespace ST_b7b3b9014239412092780528a3cf13ae
{
    /// <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()
        {


            Dts.TaskResult = (int)ScriptResults.Success;
            try
            {

             
            int rowCount = (int)Dts.Variables["User::rowsCount"].Value;

            var outFile = Dts.Variables["User::outpuDailyFile"].Value.ToString();

            var fileText = AddHeaderAndFooter.GetFileText(outFile);
            var footerText = "FOOTER|" + rowCount.ToString("0000000000") + "|||||||||||||||||||||||||||||||||" ;
             //   var headerText = "source|request_type|change_type|exclusion_reason|provider_type|social_group|status|effective_date|earliest_effective_date|change_date|term_date|latest_term_date|tin|npi|provider_id|provider_last_name|provider_first_name|provider_middle_initial|clinic_name|t50_specialty|t50_provider_type|t15_taxonomy|license_number|license_state|license_issue_date|svc_address_line1|svc_address_line2|svc_city|svc_state|svc_zip|bus_address_line1|bus_address_line2|bus_city|bus_state|bus_zip|sub_specialty";
            var fullText =  fileText  + footerText;
            AddHeaderAndFooter.WriteToFile(outFile, fullText);
             }

            catch (Exception ex)
            {
                Dts.Events.FireError(0, "ERROR", ex.Message, null, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;

            }

        }

        public static class AddHeaderAndFooter
        {
            public static int CountRecords(string filePath)
            {

                return (File.ReadAllLines(filePath).Length + 2);

            }

            public static string GetFileText(string filePath)
            {
                var sr = new StreamReader(filePath, Encoding.Default);

                var recs = sr.ReadToEnd();

                sr.Close();

                return recs;
            }

            public static void WriteToFile(string filePath, string fileText)
            {

                var sw = new StreamWriter(filePath, false);

                sw.Write(fileText, Encoding.ASCII);

                sw.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

	}
}

Which throws an Out Of Memory exception. Is there a way to optimize this script. BTW, it also happens to view it as I found that it uses outdated column names.
SQL Server Integration Services
0 comments No comments
{count} votes

Answer accepted by question author
  1. Akhil Gajavelly 645 Reputation points Microsoft External Staff Moderator
    2025-10-08T04:38:18.8766667+00:00

    Hi @Naomi Nosonovsky,

    You're getting Out of Memory because the script reads the whole 3M-row file into memory using ReadToEnd().
    Replace ReadToEnd() and WriteToFile() logic with append-mode streaming. SSIS will handle large files without OutOfMemory exceptions.

    Thanks,
    Akhil.

    1 person found this answer helpful.
    0 comments No comments

Answer accepted by question author
  1. Jay Pham (WICLOUD CORPORATION) 1,915 Reputation points Microsoft External Staff
    2025-10-09T03:17:01.6733333+00:00

    Hi @Naomi Nosonovsky,

    Thank you for reaching out regarding the Out of Memory exception in your SSIS Script Task when adding a footer to a large file (3M rows). After reviewing your description, the primary cause is loading the entire file into memory via ReadToEnd().

    As a first and most efficient fix, I recommend switching to a streaming approach: Read and write the file line by line to avoid full memory loading. This keeps usage low (under 100MB) even for GB-sized files. Here's a revised C# script for your Main() method—replace your current code and test on a small file first:

    public void Main()  
    {  
        string inputFile = Dts.Variables["User::outpuDailyFile"].Value.ToString();  
        string tempFile = inputFile + ".tmp";  
        string footer = $"FOOTER|{Dts.Variables["User::rowsCount"].Value}|..."; // Customize footer  
    
        try  
        {  
            // Step 1: Stream copy original to temp  
            using (var reader = new StreamReader(inputFile, Encoding.Default))  
            using (var writer = new StreamWriter(tempFile, false, Encoding.ASCII))  
            {  
                string line;  
                while ((line = reader.ReadLine()) != null)  
                {  
                    writer.WriteLine(line);  
                }  
            }  
    
            // Step 2: Append footer to temp  
            using (var writer = new StreamWriter(tempFile, true, Encoding.ASCII))  
            {  
                writer.WriteLine(footer);  
            }  
    
            // Step 3: Replace original with temp  
            File.Delete(inputFile);  
            File.Move(tempFile, inputFile);  
    
            Dts.TaskResult = (int)ScriptResults.Success;  
        }  
        catch (Exception ex)  
        {  
            Dts.Events.FireError(0, "Script Task", ex.Message, string.Empty, 0);  
            Dts.TaskResult = (int)ScriptResults.Failure;  
        }  
    }  
    

    I hope this helps! If you agree sugestion, feel free to mark this as final answer to your question!

    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.