SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Custom Handling Flat File Output of an SSIS Package with the Script Component

I recently had a requirement to remove the last Carriage Return <CR> and Line Feed <LF> characters when outputting a flat file from an SSIS Package.

My initial thoughts were to output the file, pick it up with a Script Task in the Control Flow, opening and manipulating the data.

I would strip away the characters I needed to and then persist a final copy of the file.  Needless to say, this did not come across as particularly elegant.

What I needed was a way to handle my requirements within the flow of the Package and persist the file just the once.

I settled on using a Script Component to handle it.

The Script Component enables you write custom code within a Data Flow Task to handle data as a Source, Transformation or Destination.

 

The Implementation

First off, I create an SSIS Package, adding a Data Flow Task to the Control Flow.

I’ll add File Connections Managers that represent both the source file that I’ll be loading and the destination file that I’d like to create.

Within the Data Flow Task, I add a Flat File Source to load my file data into the Package.

Finally,  I add the Script Component configuring it to act as a Destination.

 

I’ll be using the tab-delimited test file below.  You will notice that all the lines including the last one ends with a Carriage Return(CR) and a Line Feed (LF) Character.

 

You will need to configure the Script Component, specifying the Connection Managers it would have access to.

Here’s the code encapsulated therein.

First we get access to the file defined in the File Connection Manager, creating a new StreamWriter Object so we can write our output rows to it.

We can do this in the PreExecute Method below.

List<String> rows = new List<string>();

StreamWriter file:
object connMgr;

Public override void PreExecute()
{
   base.PreExecute();

   connMgr = Connections.OutputFile.AcquireConnection(null);
   file = new StreamWriter((string)connMgr);
}

The ProcessInputRow Method below enables you define operations you would like to action against each row of the output.

In our case, we are simply writing the output to a holding List object which we would be manipulating at the Post Execute stage of the Script Components processing.

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

Once all the rows have passed through the Script Component, the PostExecute() Method is executed.

This is where we will be handling the requirement of stripping away the characters we don’t want.

We simply iterate over the List of rows in the List Object.  We use the WriteLine() Method of the StreamWriter Object to write the row complete with a Carriage Return and Line Feed to the file.

The Write() Method can be used to write a row to a file with no line terminators. We use this Method when we determine that we are processing the last row in the List Object.

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();
}

With that all done, we close our connections to the file , the code exits and the Package completes successfully.

 

You can see the file without Carriage Return and Line Feed characters on the last row below.

 

On the Fringe

Chim Kalunta is a Database Systems Developer and Independent Consultant working out of the UK. An MCITP: Database Developer and an MCITP: Database Administrator, he can be reached at chimkalunta.com.

Comments

Leave a comment on the original post [chimkalunta.com, opens in a new window]

Loading comments...