How to set up columns in Flat File Destination in SSIS?

Question

I have an OLE DB data source and a Flat File Destination object in the data stream of my SSIS project. The goal is to simply load the data into a text file, and it does.

I have formatting problems. I need rtrim () to have multiple columns to remove trailing spaces, and I have a couple more that need their leading zeros. The current process loses all leading zeros.

rtrim () can be done by simply truncating and ignoring truncation errors, but it is very inelegant and error prone. I would like to find a better way, for example, to execute the rtrim () function where necessary.

Studying these SSIS SS questions and answers on SO, it seems that this is a “Use Script Task”, but it is just simply thrown away without any details, and it is not an intuitive thing to set up at all.

I do not see how to use scripts to do what I need. Am I using a Script task in a control flow or a Script component in a data stream? Can I do rtrim () and pad strings where necessary in a script? Has anyone got an example of this or similar?

Thank you very much in advance.

Answer 1

There are many possible solutions with SSIS! From what you mentioned, you can use the Derived Column transform in the data stream to perform cropping and indentation - you would use an expression to do this, it would be relatively simple. For instance,

ltrim([ColumnName]) 

to crop and something along the lines

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

for input (this does not match my head, so the syntax may not be accurate).

As for the scripting method, this is also true. You would use the Script Component Transform in the data stream and use VB.NET or C # string management methods (if you have 2008) (e.g. strVariable.Trim ()).