How to export data from database tables to an XML file using SSIS

I had a project where I had to generate a xml file with data from database tables using SSIS.

The components involved were

Data Flow Task
OLE DB Source - which had the SQL command at the stored proc that called the XML data
Flat File Destination  that used a Flat File Connection Manager.

The important thing to remember here was the  FFCM set up .
Here are the screen shots to show I set it up .

The DFT consisted of OLEDB Sour Editor calling a procedure that generated XML data.

Below is the Flat File Connection Manager used. The format was DELIMITED.

 

 

In the Columns section a new column COL_XML is added which is the ONLY column returned by the stored procedure that has the XML data.

 

 

 

 



The final important part was the below where we set up the data type which had to be Unicode text stream