How do I tweak columns in a Flat File Destination in SSIS?

Question

I have an OLE DB Data source and a Flat File Destination in the Data Flow of my SSIS Project. The goal is simply to pump data into a text file, and it does that.

Where I'm having problems is with the formatting. I need to be able to rtrim() a couple of columns to remove trailing spaces, and I have a couple more that need their leading zeros preserved. The current process is losing all the leading zeros.

The rtrim() can be done by simple truncation and ignoring the truncation errors, but that's very inelegant and error prone. I'd like to find a better way, like actually doing the rtrim() function where needed.

Exploring similar SSIS questions & answers on SO, the thing to do seems to be "Use a Script Task", but that's ususally just thrown out there with no details, and it's not at all an intuitive thing to set up.

I don't see how to use scripting to do what I need. Do I use a Script Task on the Control Flow, or a Script Component in the Data Flow? Can I do rtrim() and pad strings where needed in a script? Anybody got an example of doing this or similar things?

Many thanks in advance.

Answer 1

With SSIS, there are many possible solutions! From what you mention, you could use a Derived Column transform within a Data Flow to perform the trimming and padding - you would use an expression to do this, it would be relatively straightforward. Eg,

ltrim([ColumnName])

to trim and something along the lines of

right("0000"+ [ColumnName],6)

to pad (this is off the top of my head so syntax may not be exact).

As for the scripting method, that is also valid. You would use the Script Component Transform on the Data Flow and use VB.NET or C# (if you have 2008) string manipulation methods (eg strVariable.Trim()).