BCP Out Custom Format Data to Flat File

One of the frequent requirements that I have come across in most of the ETL projects which I’ve undertaken is the ability to transfer data from and to flat files. The usual way to implement this was by designing a SSIS package with a data flow component setup to do the transfer. The metadata of file as well as delimiter has to be consistent for this. In one of the recent cases, the requirement was to generate the file in a custom format which was not possible with the default data flow task method. This post explains solution which I designed to create the file with data extract in the required custom format.
The requirement can be illustrated using the below example
There is a table storing order details with fields as OrderID, OrderDesc, OrderDate, ReferredBy and sample data can be represented as follows

The requirement is to export data to text file in the below format

This cant be achieved using SSIS data flow task because of the custom format required. If you check the flat file destination task on SSIS the properties look like below

As you see from above the column delimiter has to be consistent in case of SSIS data flow and its static.In our case we required different delimiters which makes it impossible to use the flat file destination task.
Requirements like this can be very easily achieved using bcp with the help of a format file.
BCP provides us with the flexibility of specifying custom formats using format file. Lets see how a format file can be used in the above case to get our required output format.
The bcp command will look like below

bcp “SELECT * FROM DBName.dbo.Orders” queryout “F:OrdDetOP
.txt” -f “F:OrderExpFormat.fmt” -S “ServerName” -T

assuming you’re using windows authentication in the server.
In case of sql authentication just change the command to

bcp “SELECT * FROM DBName.dbo.Orders” queryout “F:OrdDetOP
.txt” -f “F:OrderExpFormat.fmt” -S “ServerName” -U Username -P password

The format file will look like below for the required output.

As you see from above we provide the delimiter information in such a way to get desired output. The catch lies in fact that for 3rd column (OrderDate) we give delimiter as rn followed by string ReferredBy to make sure we get the last column value with required header name in the next line. Similarly we give delimiter information that includes newline character and line to insert line separator between each of the rows. Similar type of slight tweaks in format file will help us to achieve custom file formats while using bcp.
One thing to remember is that bcp is a command line utility so if you want to implement this in sql you need to either execute the command using execute process task in SSIS or use T-SQL script to execute bcp using xp_cmdshell extended procedure.
The execute process task will look like this in case you want to implement this in SSIS

Hope this explains how format file can be used in achieving custom data formats while exporting to text file.