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

Question

I'm still new in SSIS,

Now I can read the SQLStatement, FileName, and FileLocation based on this video

But I'm facing the problems when export the flat file base on the File Location in SQL Table.

You can check my sample package. Besides, the following screenshot shows the SQL table structure where information is stored:

Different FileName, Different File Location and different SQL Command

Answer 1

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

Answer 2

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.

Answer 3

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.