Remove last row from file with SSIS Script Component

September 14, 2011

The other day I had a unique request from a client.  I had been working on a package that would combine a number of smaller files into one larger file and then copy it to a new directory.  An easy enough task using SSIS and some staging tables to insure that the data was sorted in the correct order.  One of the issues that came up was how they were importing the file.  For some reason the last CR/LF at the end of the file, used to start a new line, was showing up as a bad character and causing the process to fail, so naturally they wanted me to remove it.  I didn’t have a clue at fist how I was going to accomplish this, but after asking around the office one of the guys said that it could be done by using a script component.

Well now I at least had a starting off point.  So to Bing and Google I went looking for the most uncomplicated method I could find, since VB and C# are not exactly in my wheel house.  After searching several forums and asking my question in a multitude of ways I finally found what I was looking for. It’s a pretty short piece of script which deletes the last to characters of the end of a file if they match CR/LF.

‘ Microsoft SQL Server Integration Services Script Task ‘ Write scripts using Microsoft Visual Basic ‘ The ScriptMain class is the entry point of the Script Task.

Imports System.IO

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

‘ The execution engine calls this method when the task executes.

‘ To access the object model, use the Dts object. Connections, variables, events,

‘ and logging features are available as static members of the Dts class. ‘ Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

‘ ‘ To open Code and Text Editor Help, press F1.

‘ To open Object Browser, press Ctrl+Alt+J.

Sub Main()

Dim fileStream As FileStream

fileStream = File.Open(“myfile.txt”, FileMode.Open, FileAccess.ReadWrite)

If fileStream.Length > 1

Then fileStream.Seek(-2, SeekOrigin.End)

Dim bytes(1) As Byte

fileStream.Read(bytes, 0, 2)

If bytes(0) = 13 And bytes(1) = 10 Then

fileStream.SetLength(fileStream.Length – 2)

End If

End If


End Sub

End Class


I added the script and changed the file name and it worked perfectly.  I hope this helps you guys avoid the time wasted searching for the same thing.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: