Export Dynamic Query Results to Flat File Using SSIS

I've had a request from one of my regular blog followers couple of days back regarding a requirement.
The requirement  was to create a package which would enable exporting results of sql query to a flat file. The catch here was that query cant be determined beforehand.
Exporting to flat file is straightforward in SSIS using a data flow task but for this the metadata (column structure) has to be fixed. So that was not a viable option here. So I used a different approach and quickly put together a sample package for him. I'm sharing the package here for others benefit.
The package was as simple as below

The first task is a simple Execute SQL Task which will have properties set as follows
1. ConnectionType : OLEDB
    Connection: Connection manager created pointing to your server and database from which data is to be          exported
2 SQLSourceType: Variable
   SourceVariable : This is pointed to a user variable created in the package which holds the query as the          value. This can be added to configurations within package if you need to pass the value from outside.
3. Result Set: Full result set
4. Result Set tab: the resultset 0 mapped to your Object variable created in SSIS

This will make sure the object variable is populated with the results of your query passed. Once this is done next part is to get this result written to the flat file. You can create a variable to set the path of the file and also add it to the configurations. The Script task can be given as below

Public Sub Main()
'
' Add your code here
        '
        Dim x As New OleDb.OleDbDataAdapter
        Dim dt As New DataTable
        Dim str As String = vbNullString
       

        Dim i As Int16
        x.Fill(dt, Dts.Variables("ADOres").Value)

        i = dt.Columns.Count
        For j As Int16 = 0 To i - 1
            str = str & dt.Columns.Item(j).ColumnName & IIf(j = i - 1, vbCrLf, ",")
        Next


        i = dt.Rows.Count

        For j As Int16 = 0 To i - 1

            str = str & Join(dt.Rows.Item(j).ItemArray(), ",") & vbCrLf

        Next
        System.IO.File.WriteAllText(Dts.Variables("FileName").Value.ToString(), str)
        
        Dts.TaskResult = ScriptResults.Success

    End Sub

The code will create a datatable and fill it with the contents of the ADO recordset created in previous step using OLEDB Data Adapter. Once this is done then then it just takes two loops, first one to get column names from Recordset and second one to get the row values from the datatable. We take row values as a delimited string array using Join() function and appends to a string variable along with the column names. At the end of the looping logic string variables will have entire contents to be written to file.
An object of System.IO.File is created and WriteAllText method is invoked to write contents of string variable to the flat file. Once this is done flat file gets populated with the required data as per the given query. This is much flexible compared to data flow method as we dont need to fix the metadata here at runtime.

The sample package and config can be accessed from the below link
Package link: https://drive.google.com/file/d/0B4ZDNhljf8tQTVhTd3dvc2JWN2c/edit?usp=sharing
Config link: https://drive.google.com/file/d/0B4ZDNhljf8tQWjFoY3hUaVc5dTQ/edit?usp=sharing

Make sure you change config properties to your server database and file path values before you start running the package.
In case of any issues or should you need any more clarification on any of the above, feel free to revert with comments.