SSIS包复用块：如何将数据流数据传入控制流避免临时文件？

1. Package Parameter Setup #

• Create a package parameter @DataSourceSelector (string type) with allowed values SQLServer or Cobol. This determines which data source flow runs at startup.

2. Data Flow Tasks for Each Source (No Temp Files) #

Build two separate Data Flow Tasks (one for SQL Server, one for Cobol) that load data directly into an Object variable instead of flat files:

• For the SQL Server flow:
  • Add an OLE DB Source pointing to your SQL Server database, configure the query to fetch required data.
  • Add a Recordset Destination component. In its properties, set the VariableName to a package-level Object variable (e.g., @ProcessData). Map all required columns from the source to the recordset.
• For the Cobol flow:
  • Use a Flat File Source (or appropriate Cobol data parser component) to read and parse the Cobol data.
  • Add a Recordset Destination pointing to the same @ProcessData Object variable, mapping columns correctly.

3. Control Flow Logic to Select Data Source #

• Place the two Data Flow Tasks in the control flow.
• Add a Sequence Container named CommonProcessing to hold reusable post-load steps.
• Create precedence constraints from each Data Flow Task to the CommonProcessing container:
  • For the SQL Server flow's constraint: Set to Expression and Constraint, with the expression @[$Package::DataSourceSelector] == "SQLServer".
  • For the Cobol flow's constraint: Use the expression @[$Package::DataSourceSelector] == "Cobol".
• This ensures only the selected data source runs, populating @ProcessData for the common steps.

4. Common Processing with Foreach Loop #

Inside the CommonProcessing container:

• Add a Foreach Loop Container. Configure its enumerator as Foreach ADO Enumerator, and select @ProcessData as the ADO object source variable.
• In the Variable Mappings tab, map each column from the recordset to individual package variables (e.g., @CustomerID, @CustomerName). These variables will populate with values from each row in the recordset.
• Inside the loop:
  1. Script Task to Generate JSON:
     • Add a Script Task, set ReadOnlyVariables to all mapped column variables.
     • In the script (C# example), construct a JSON string using variable values:

       string json = $"{{\"CustomerID\": \"{Dts.Variables["CustomerID"].Value}\", \"CustomerName\": \"{Dts.Variables["CustomerName"].Value}\"}}";
       // Write to dynamic filename, e.g., "Customer_"+Dts.Variables["CustomerID"].Value+".json"
       System.IO.File.WriteAllText(@"C:\Output\" + $"Customer_{Dts.Variables["CustomerID"].Value}.json", json);
       

  2. Upload Task:
     • Use an FTP Task (for standard FTP) or a Script Task (for SFTP/HTTP) to upload the generated JSON file to your server. For SFTP, use libraries like SSH.NET in the script to handle transfers.

Key Notes #

• The Recordset Destination stores data in memory as an ADO recordset, eliminating the need for temporary flat files.
• Ensure @ProcessData is a package-level variable to be accessible across all tasks.
• When mapping variables in the Foreach Loop, column indexes must match the order defined in the Recordset Destination.
• For very large datasets, in-memory storage may impact performance—consider chunking data if needed, but this approach works for most standard volumes.

内容的提问来源于stack exchange，提问作者schinois