ravella ravella - 3 years ago 168
SQL Question

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

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.

Answer Source

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.

  1. 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
    
  2. Use a Derived Column Transformation to do a similar thing.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download