The following article describes how T-SQL query results can be passed to SSIS variables. These values can then be passed as call parameters to the Xtract IS tasks.
The T-SQL query on existing SQL table objects is a single value that is then passed to a dedicated selection criterion.
The depicted example uses the Xtract BAPI task, which dynamically executes the FISCYEAR selection field at runtime.
Note: This example case can also be used for other Xtract IS tasks within SQL Sever Integration Services (SSIS).
Step-by-Step Guide #
- Drag & Drop the SSIS Task Execute SQL Task to the Control Flow canvas (1).
- Create a SSIS-Variable of data type String, e.g. @[User::myGJAHR] (2).
- Use the correct OLE-DB connection to the SQL Server Database, where the required Table values already exists.
- Edit the Execute SQL Task and click on the Build Query button (3).
- Select the required T-SQL statement to fetch the needed value, e.g. fiscal year column MSEG~MJAHR: SELECT MAX(MJAHR) AS maxMJAHR FROM MSEG.
- Open the Result Set configuration and map the result of the T-SQL statement to the defined SSIS variable @[User::myGJAHR] (4).
- Proceed with configuring a Data Flow task with the Xtract BAPI task and corresponding OLE-DB task for processing the BAPI result.
- Select the defnied SSIS variable myGJAHR to the corresponding selection criterion (5).
- Execute the SSIS package and check the results with known built-in functionalities.