CODEWITHSUNDEEP
sql-servercsvssis
Mike C
Mike C

Reputation: 63

SSIS 2012: Trim trailing commas from a CSV output

I have a file, I cannot control the output, that has a trailing comma on each data row and not the header row.

"Pid","Dtdate","Tencode","Stype","Amount","Currentbal"
"13773793","05/26/2014","Abc123","PAYMENT","50.00","1807.95",
"13773794","05/26/2014","Abc123","ADJUSTMENT","10.00","18.95",
"",

I am trying to figure out how to trim each row of the trailing comma so that it will read in SSIS correctly.

"Pid","Dtdate","Tencode","Stype","Amount","Currentbal"
"13773793","05/26/2014","Abc123","PAYMENT","50.00","1807.95"
"13773794","05/26/2014","Abc123","ADJUSTMENT","10.00","18.95"
""

I assume I would use a Script Transformation but that is where I get stuck, I don't get to program much so I am pretty novice in the VB Script Tasks.

Might be worth mentioning this is in a FOR each loop to grab all files within a folder. There is usually only one file but I am making room for the potential of multiples and the file name is a variable expression of the filename: @[User::FileName]

My assumption is that once the file is dynamically generated that you lose the ability to dictate the column rules. That is why I was looking for a way to trim the trailing comma of each row.

The error that I am getting is 

[Flat File Source [18]] Error: The column delimiter for column "Currentbal" was not found. 
[Flat File Source [18]] Error: An error occurred while skipping data rows.

I assume it is because the trailing comma is telling the system there should be another column with no associated header. Once trailing commas are removed the file processes fine.

Upvotes: 1

Views: 1868

Answers (2)

billinkc
billinkc

Reputation: 61201

An even simpler approach would be to change your connection manager to skip the header row.

  • Header rows to skip: 1
  • Column names in the first data row: unchecked

enter image description here

Upvotes: 1

TMNT2014
TMNT2014

Reputation: 2130

Try setting the column delimiter for the last column as shown below.You will need to type this in the box and not use the Combobox selection. (Or just select {LF} and edit the text and put a Comma before it) enter image description here

Upvotes: 1

Related Questions