The SQL Execute task in SSIS allows you to execute a SQL that would return a single row with multiple columns and then map these columns to variables within the package. To do this,
- In the General section set the Resultset property to “Single row”.
- Set the SQL statement.
- In the Result Set section, set the Result Name to 0 – n and the corresponding variables to be mapped. Ensure that the column order is right. I believe you can also use the column name but haven’t tried that.