removing extra space in last row using ssis

Question

I have a csv file which contains few rows, columns. I am having issue at source because the last row has an extra space. How can i remove this space?

You can see the following image has an extra space at the end

Answer 1

Since you are open to powershell, I have used this method. It's important that the last think in each file is your LF/CR. If it could be missing then you'd have to put a check in there for that.

Single File

$stream = [IO.File]::OpenWrite("somefile.csv")
$stream.SetLength($stream.Length - 2)
$stream.Close()
$stream.Dispose()

Multiple Files in a directory

#Remove the last bit of data from the end of the file, which is a LF, so BULK INSERT doesn't break
$fileDirectory = "E:\dir\subdirectory"

foreach($file in Get-ChildItem $fileDirectory)
{
    $filePath = $fileDirectory + "\" + $file

    $stream = [IO.File]::OpenWrite($filePath)
    $stream.SetLength($stream.Length - 2)
    $stream.Close()
    $stream.Dispose()
}

I use this on a pretty large daily dataset and it's stupid fast, a second or two. Original idea credited to Joey via this answer.

FROM THE OP