When working with SSIS Packages in SQL Server, there may be situations where you need to remove the last Carriage Return (CR) and Line Feed (LF) characters when outputting a flat file. In this article, we will explore a solution to handle this requirement within the flow of the Package.

One approach to tackle this issue is to output the file, pick it up with a Script Task in the Control Flow, open and manipulate the data, and then persist a final copy of the file. However, this approach may not be the most elegant solution.

Instead, we can utilize the Script Component within a Data Flow Task to handle the data as a Source, Transformation, or Destination. This allows us to write custom code to manipulate the data and persist the file just once.

Let’s walk through the implementation steps:

1. Create an SSIS Package and add a Data Flow Task to the Control Flow.
2. Add File Connection Managers to represent both the source file that will be loaded and the destination file that we want to create.
3. Within the Data Flow Task, add a Flat File Source to load the file data into the Package.
4. Add a Script Component and configure it to act as a Destination.

Now, let’s take a look at the code encapsulated within the Script Component:

object connMgr;
StreamWriter file;

public override void PreExecute()
{
   base.PreExecute();
   connMgr = Connections.OutputFile.AcquireConnection(null);
   file = new StreamWriter((string)connMgr);
}

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
   rows.Add(Row.FirstName + "\t" + Row.Surname);
}

public override void PostExecute()
{
   base.PostExecute();
   int i = 0;
   foreach(String row in rows)
   {
      i++;
      if (i == rows.Count)
      {
         file.Write(row);
      }
      else
      {
         file.WriteLine(row);
      }
    }
    Connections.OutputFile.ReleaseConnection(connMgr);
    file.Close();
}

In the PreExecute() method, we acquire the connection to the file defined in the File Connection Manager and create a new StreamWriter object to write the output rows to it.

The Input0_ProcessInputRow() method allows us to define operations to be performed against each row of the output. In this case, we are simply writing the output to a holding List object.

Once all the rows have passed through the Script Component, the PostExecute() method is executed. Here, we iterate over the List of rows and use the WriteLine() method of the StreamWriter object to write each row with a Carriage Return and Line Feed to the file. We use the Write() method when processing the last row to write it without line terminators.

Finally, we close the connections to the file, release the connection manager, and close the file.

With this implementation, the SSIS Package will successfully remove the Carriage Return and Line Feed characters from the last row of the flat file.

By utilizing the Script Component within a Data Flow Task, we can handle data manipulation requirements more elegantly and efficiently in SQL Server.