Export Flat File based on the each SQL statement in the table and destination

Important Note

Storing SQL Commands and file locations within databases and using them within a dynamic data import process is not recommended from a security perspective.

TLDR

You must add a Data Flow Task that contains an OLE DB Source and Flat File Destination configured to read from the SSIS variables.

Details

1. First, make sure that you used the variables mapping in the for-each loop container to retrieve all information about the source (SQL command) and destination (Flat file path and configuration).

• Looping Through a ResultSet with the ForEach Loop

2. Next, you should add a Flat File connection manager (for the destination) and configure the connection string to read from the [File Location] and [File Name] variables.

• SSIS: Dynamic File Name for Flat File Destination
• Dynamic Flat File Connections in SQL Server Integration Services

3. Third step is to add a Data Flow Task within the Foreach loop container, where you should add an OLE DB Source and a Flat File Destination.

4. Set the OLE DB Source "Data Access Mode" to SQL Command from variable and select the variable you have mapped to the SQL Command column.

5. Configure the Flat File Destination to use the Flat File Connection Manager created previously.

Side Note

Check the following detailed article. It explains a very similar case:

• Implementing Foreach Looping Logic in SSIS

This is not an answer to your question, but a simplification. A view on the table that provides the complete bcp statement would avoid a lot of parameters. (Can @@Servername be the instance?) Then SSIS can query the view and run each command.

Edit: added a bcp to add column names to the file

SELECT 
    'bcp "SELECT name from sys.columns WHERE object_id = OBJECT_ID(''' 
        + SUBSTRING(@SqlCommand, CHARINDEX(' FROM', @SqlCommand) + 6, 8000) + ''')' 
        + '" queryout " + FileLocation + '\' + FileName + '" '
        + '-S"' + @@SERVERNAME + '" -d"' + DatabaseName + '" -t, -c -T' 
        as [BcpCmdColumnNames],
    'bcp "' + SqlCommand + '" queryout " + FileLocation + '\' + FileName + '" '
        + '-S"' + @@SERVERNAME + '" -d"' + DatabaseName + '" -t, -c -T' 
        as [BcpCmdTableData]
FROM dbo.Falina_Config

I might also avoid SSIS and use a Job Step running PowerShell.exe. Here is a possible PowerShell script using DBATools. (You can use any alternate to do the query without DBATools.)

https://www.mssqltips.com/sqlservertip/6172/execute-powershell-script-from-ssis-package/

https://dbatools.io/

Edit: Added query for column names

param( 
    [string] $instance = 'myinstance', 
    [string] $dbname = 'mydatabase'
) 

$queryExtracts = 'select DatabaseName, SqlCommand, FileLocation, FileName from dbo.Falina_Config'

$extracts = Invoke-DbaQuery -SqlInstance $instance -Database $dbname -Query $query -CommandType Text | 
    select DatabaseName, SqlCommand, FileLocation, FileName

foreach ($extract in $extracts) {

    $fileLocation = $extract.FileLocation
    $fileName = $extract.FileName

    $file = Join-Path $fileLocation $fileName
    
    $databaseName = $extract.DatabaseName
    #$queryColumnNames = "select name from sys.columns where object_id = OBJECT_ID('[$($extract.TableSchema)].[$($extract.TableName)]')"
    #$queryTableData = "select * from [$($extract.TableSchema)].[$($extract.TableName)]" # this might be safer
    $querySqlCommand = $extract.SqlCommand 

    #bcp "$queryColumnNames" queryout "$fileName" -S"$instance" -d"$databaseName" --% -t, -c -T
    #bcp "$queryTableData" queryout "$fileName" -S"$instance" -d"$databaseName" --% -t, -c -T
    bcp "$querySqlCommand" queryout "$fileName" -S"$instance" -d"$databaseName" --% -t, -c -T

}

The --% stops PowerShell from parsing or replacing the stuff in the rest of the line. For example, it would not like the comma.

Warning, I did not test any of this so expect it to require modification. It would be amazing if I got the quoted quotes right.

Your biggest challenge using SSIS for this is that (I assume) your differing SQL statements are going to return differing sets of columns. SSIS Data Flows and Flat File Destinations do not support dynamic columns, in fact they are very rigid.

So instead I would configure the Execute SQL Task to return a Result Set into a variable, then connect a Script task inside your Loop. The Script would need to loop over the Result Set variable's rows and columns, and write them out to the variable destination file.

1. Use Script Task
2. Create a Variable.
3. Enter the variable to the ReadOnlyVariables: User::FileDelimiter,User::FileExtension,User::LogFolder,User::SQLStatement

its also include the script at the back end

public void Main()
        {
            string datetime = DateTime.Now.ToString("yyyyMMddHHmmss");
            try
            {

                //Declare Variables
                string SQLStatement = Dts.Variables["User::SQLStatement"].Value.ToString();
                string FileDelimiter = Dts.Variables["User::FileDelimiter"].Value.ToString();
                string FileExtension = Dts.Variables["User::FileExtension"].Value.ToString();



                //USE ADO.NET Connection from SSIS Package to get data from table
                SqlConnection myADONETConnection = new SqlConnection();
                myADONETConnection = (SqlConnection)(Dts.Connections["DBConn"].AcquireConnection(Dts.Transaction) as SqlConnection);


                //Read list of Tables with Schema from Database
                string query = "SELECT FilesName,FileDestination,SQLStatement FROM "+ SQLStatement;

                //MessageBox.Show(query.ToString());
                SqlCommand cmd = new SqlCommand(query, myADONETConnection);
                DataTable dt = new DataTable();
                dt.Load(cmd.ExecuteReader());

                //Loop through datatable(dt) that has schema and table names
                foreach (DataRow dt_row in dt.Rows)
                {
                    string FileName = "";
                    string FileDestination = "";
                    string TableName = "";
                    object[] array = dt_row.ItemArray;
                    FileName = array[0].ToString();
                    FileDestination = array[1].ToString();
                    TableName = array[2].ToString();

                    string FileFullPath = FileDestination + FileName; //DestinationFolder + "\\" + SchemaName + "_" + TableName + "_" + datetime + FileExtension;

                    //Get the data for a table into data table 
                    string data_query = TableName; //"SELECT * FROM [" + SchemaName + "].[" + TableName + "]";
                    SqlCommand data_cmd = new SqlCommand(data_query, myADONETConnection);
                    DataTable d_table = new DataTable();
                    d_table.Load(data_cmd.ExecuteReader());

                    StreamWriter sw = null;
                    sw = new StreamWriter(FileFullPath, false);

                    // Write the Header Row to File
                    int ColumnCount = d_table.Columns.Count;
                    for (int ic = 0; ic < ColumnCount; ic++)
                    {
                        sw.Write(d_table.Columns[ic]);
                        if (ic < ColumnCount - 1)
                        {
                            sw.Write(FileDelimiter);
                        }
                    }
                    sw.Write(sw.NewLine);

                    // Write All Rows to the File
                    foreach (DataRow dr in d_table.Rows)
                    {
                        for (int ir = 0; ir < ColumnCount; ir++)
                        {
                            if (!Convert.IsDBNull(dr[ir]))
                            {
                                sw.Write(dr[ir].ToString());
                            }
                            if (ir < ColumnCount - 1)
                            {
                                sw.Write(FileDelimiter);
                            }
                        }
                        sw.Write(sw.NewLine);

                    }

                    sw.Close();

                    Dts.TaskResult = (int)ScriptResults.Success;
                }

            }

            catch (Exception exception)
            {

                // Create Log File for Errors
                using (StreamWriter sw = File.CreateText(Dts.Variables["User::LogFolder"].Value.ToString() + "\\" +
                    "ErrorLog_" + datetime + ".log"))
                {
                    sw.WriteLine(exception.ToString());
                    Dts.TaskResult = (int)ScriptResults.Failure;


                }
            }
        }