SSIS Flat File destination Comma separated after each fixed column value (After Fixed Length of column)

Question

I've spent almost a day to find a solution for this, but so far no luck.

Here is the problem: In my SSIS Package I'm trying to export data from SQL server table to flat file destination with fixed length option followed by comma.

Ex:  Account     Address          Zip
     123456    ,2525 Est dr     ,77077
     563       ,12 we dr        ,75023

If you see the above example the Account is 10 character length, and Address is 16 character length.

So I need write like this to a flat file with fixed length followed by comma (in other words comma should come between the columns)

But the in flat file destination of SSIS, I am not able to set both Fixed length and comma delimiter at the same time. Either one is working , but not both.

Please suggests me the options, to get this behavior.

Troy Witthoeft Troy Witthoeft · Accepted Answer · 2017-10-02T01:17:14

That is an odd schema for a flatfile, but OK. Set the flatfile to be fixed with. Then pad the columns with trailing spaces and a comma yourself. Two methods come to mind.

Update your OLE DB Sourcecomponent to use a SQL statement that does the padding for you ..

SELECT 
    Account + REPLICATE(' ',10 - LEN(Account)) + ',',
    Address + REPLICATE(' ',16 - LEN(Address)) + ',',
    Zip + REPLICATE(' ',5 - LEN(Zip)) + ','
FROM MyTable

Use a Derived Column Transformation to do a similar thing.

SSIS Flat File destination Comma separated after each fixed column value (After Fixed Length of column)

1 Answers