Remove Empty Row from CSV File Using PowerShell in SSIS

When you export data to a flat file destination in SSIS you end up with an empty row at the end of the file. This is by design, because each row requires a CR LF. That last row of data still has a carriage return at the end of it thus the blank row that follows.

Finding a solution to this problem is not straightforward and that is likely due to the fact this is not really a problem, but not everyone will agree with that opinion. If you have been Googling you probably stumbled upon the conditional split solution, but that only works if you are starting from a csv that has the trailing blank row and you are writing that data to some place other than a flat file destination. We want to remove the empty row after we write to the flat file destination.

If you are not a C# developer or a VB developer (nobody should be one of these actually), then you might struggle with the script task in SSIS. Instead of that we are going to invoke a process, PowerShell.exe. Here are the steps you will need to follow:

• Make sure PowerShell is installed/enabled on the server that the package runs on.

• On the server where your SSIS package is executed, we need to create the PowerShell script to remove the blank row (below). Replace the value in the $textFile variable to your target file. Save the script in a directory where the SSIS service account can execute the file and modify the file, etc. Save the file as PS_RemoveBlankRow.ps1 or whatever you like.

$textFile = 'C:\YourFileShare\YourTargetFile.csv'
[System.IO.File]::WriteAllText($textFile, [System.IO.File]::ReadAllText($textFile) -replace '[\r\n]+$')

• Before you can remove the empty row from your file you first need to save the file to its final destination. So work through all the steps in your SSIS package to get your data to the desired flat file.

• Now we are ready to clean the file. In the control flow tab of your package add an Execute Process Task. Click on the Process tab, in the Executable option type PowerShell.exe, this will invoke PowerShell. In the arguments type the following (below). The –F option is for file, then you are entering the directory and file of the PS file we created in the second step. You can leave the rest of the settings to their defaults.

-F C:\MyFileShare\PS_RemoveBlankRow.ps1

• You might have to grant an exception on executing this script file if you have some kind of AV that blocks it. Depends on your environment.

There you go, a simple way to remove the trailing empty row in a CSV.