Just as usual, this post is kind of “Bi-product” of my attempt to create a demo for another post in which I need to use fixed position file. At first I tried to create a fixed position file using BCP. BCP has query out option to export query results into a file, but I was unable to produce the fileformat which uses fixed position. I actually even tried to use format file to generate required format but I ran into many issues so finally I thought that it is more important to get results than means to achieve it (at least it is true in this case 
) and I fired up Visual Studio to use SSDT.
Actually it is very easy to generate a fixed position file using SSIS. All you have to create is a simple Data Flow task and then add a SQL Server as source and a flat file as destination into that task. Only thing to be sure is configuring Flat File destination settings.
 |
 |
 |
In Flat File Connection Manager settings, goto General tab and make sure that FORMAT is “Ragged Right” not “Fixed Width”. What’s confusing is if you want to add row delimiter in the end then you have to use Ragged Right not Fixed Width. And that’s it !!
Simply run the package and you have fixed width flat file for your all 
experiments …
I have attached sample SSIS project created with SSDT to give an idea. Of course you will need to modify this project to meet your requirements.
That’s it for now…
It’s Just A Thought … 
