How to Assign Column Value From a Table to Variables Using Execute SQL Task in SQL Server SSIS?

Here is an example implemented in a package.

Needed three column values from a row in a table into three different variables. For this example we will be using table named ETL_CNTRL_TBL whose sole purpose is to store audit data of a package run i.e. start_dt ,end_dt ,package_identifier ,manual_load_flag....etc

Below images explain the setup required in ESQLTask to achieve same.

  

STEP2 : 

Viewing inside the ESQLT  General tab it is shown in below image:

Query in this example is: 

declare @extractid varchar(50)
SET @ExtractId = 'INSTLD_BASE_F'
IF EXISTS (SELECT 'x'  FROM ds_Shared.dbo.ETL_CNTRL_TBL
WHERE ExtractIdentifier = @ExtractId AND ManualEntry = 1 AND ExtractExecutionStatusCode = 0)
SELECT StartDate, EndDate, ManualEntry
 FROM ds_Shared.dbo.ETL_CNTRL_TBL
 WHERE ExtractIdentifier = @ExtractId 
AND ManualEntry = 1 AND ExtractExecutionStatusCode = 0
ELSE
SELECT StartDate, EndDate, ManualEntry
 FROM ds_Shared.dbo.ETL_CNTRL_TBL
 WHERE ExtractIdentifier = @ExtractId 
AND ManualEntry = 0

Here we are only concerned about the select statement part because that's what is going to select the three column values into the parameters.

Note: Here because we are going to return only one-row value we have to set return type as "SINGLE ROW".

STEP 3:

Assigning variables to hold values of columns in a select statement.

In the resultset tab mention the column name with the corresponding variable name where you want the value to be stored.

Remark: The Resultname must match the column name in the select statement.

This task when executed will make the 3 variables have the value of three columns specified in the SELECT statement, which can be used in by following tasks in SSIS PACKAGE.