For this you need to create a C# script task. In there you will have 2 files (listed below):

Then you need to have 2 variables you pass to the script task: This one is string type and contains the file name and path you want exported to like this C:\test.csv User::FileExportNameAndPath

This one will be an OBJECT type and contains the data set from your SQL query. You execute a SQL task and output results to OBJECT variable. User::ObjDataToSaveToExportFile

If you use the same variable names above you will not have to edit the script code below at all. You can even copy/paste the SSIS c# script task from one project to another and as long as you use the same variable names you wont have to edit anything

First one in Script Main: using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.IO; using System.Text; using System.Data.OleDb; // this is to convert he object from SSIS to a data table //using System.Text;

namespace ST_a8a7451a5662418eb87a1394e40bef29
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public void Main()
        {
            bool IncludeHeaders = true;
            string FileNameAndPath = Dts.Variables["User::FileExportNameAndPath"].Value.ToString();
            
            

            // this gets the data object and sets ti to a data table
            OleDbDataAdapter A = new OleDbDataAdapter();
            System.Data.DataTable dt = new System.Data.DataTable();
            A.Fill(dt, Dts.Variables["User::ObjDataToSaveToExportFile"].Value);

            // for test data
            //DataTable sourceTable = GetTestData();
            DataTable sourceTable = dt;

            //using (StreamWriter writer = new StreamWriter("C:\\Temp\\dump.csv")) {
            using (StreamWriter writer = new StreamWriter(FileNameAndPath))
            {
                // this calls the class in another file
                ConvertToCSV.WriteDataTable(sourceTable, writer, IncludeHeaders);
            }
    

            Dts.TaskResult = (int)ScriptResults.Success;
        }// end main

        public DataTable GetTestData() 
        { 
            
            DataTable sourceTable = new DataTable();

            sourceTable.Columns.AddRange(new DataColumn[] {
                new DataColumn("ID", typeof(Guid)),
                new DataColumn("Date", typeof(DateTime)),
                new DataColumn("StringValue", typeof(string)),
                new DataColumn("NumberValue", typeof(int)),
                new DataColumn("BooleanValue", typeof(bool))
            });

            sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String1", 100, true);
            sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String2", 200, false);
            sourceTable.Rows.Add(Guid.NewGuid(), DateTime.Now, "String3", 300, true);


            return sourceTable;
        }// end get teest data

        public static class Extensions
        {
            public static string ToCSV(DataTable table)
            {
                var result = new StringBuilder();
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    result.Append(table.Columns[i].ColumnName);
                    result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
                }

                foreach (DataRow row in table.Rows)
                {
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        result.Append(row[i].ToString());
                        result.Append(i == table.Columns.Count - 1 ? "\n" : ",");
                    }
                }

                return result.ToString();
            }
        }


        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }// end class
}// end namespace

Send File named: ConvertToCSV.cs (though you can probably combine them into the main file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;

namespace ST_a8a7451a5662418eb87a1394e40bef29
{
    class ConvertToCSV
    {

        public static void WriteDataTable(DataTable sourceTable, TextWriter writer, bool includeHeaders) 
        {
            if (includeHeaders) {
                IEnumerable<String> headerValues = sourceTable.Columns
                    .OfType<DataColumn>()
                    .Select(column => QuoteValue(column.ColumnName));
                
                writer.WriteLine(String.Join(",", headerValues));
            }

            IEnumerable<String> items = null;

            foreach (DataRow row in sourceTable.Rows) {
                items = row.ItemArray.Select(o => QuoteValue(o.ToString()));                
                writer.WriteLine(String.Join(",", items));
            }

            writer.Flush();
        }// end Write Data Table

        // this function adds quotes around the strings for text qualified values
        private static string QuoteValue(string value)
        {
            return String.Concat("\"",
            value.Replace("\"", "\"\""), "\"");
        }


    }
}