How can i use int32 variables Expression builder in SSIS

1
votes

In the expression builder, my query looks something like this. 

SELECT *
FROM TABLE_A
WHERE COLOMN_A_ID IN 
                (SELECT COLOMN_A_ID 
                 FROM TABLE_B 
                JOIN EXTRACT_STATUS  ON   EXTRACT_DTTM >  TO_DATE('" + (DT_WSTR, 30) @[User::LastTransferDT] + "', 'mm/dd/yyyy hh:mi:ss AM') AND TABLE_B.Scheduled_DT BETWEEN EXTRACT_STATUS.RANGE_BEGIN_DTTM AND EXTRACT_STATUS.RANGE_END_DTTM AND F_MPOG_EXTRACT.extract_num ="+@[User::RowNumber]+ ")"`

I keep getting the same error again and again. Please help.

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%C2%AE%20Visual%20Studio%C2%AE%202010&ProdVer=10.0.40219.1&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

The data types "DT_WSTR" and "DT_I4" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation 

SELECT *
FROM TABLE_A
WHERE COLOMN_A_ID IN (SELECT COLOMN_A_ID
                     FROM TABLE_B
                          JOIN EXTRACT_STATUS
                             ON     EXTRACT_DTTM >
                                      TO_DATE('" + (DT_WSTR,30)@[User::LastTransferDT] + "', 'mm/dd/yyyy hh:mi:ss AM')
                                AND TABLE_B.Scheduled_DT BETWEEN EXTRACT_STATUS.RANGE_BEGIN_DTTM
                                                             AND EXTRACT_STATUS.RANGE_END_DTTM
                                AND F_MPOG_EXTRACT.extract_num =" + @[User::RowNumber]" failed with error code 0xC0047080.

(Microsoft.DataTransformationServices.Controls)

BUTTONS:

OK

1
sqlsql-serveroraclessis

1 Answers

3
votes

You likely need to explicitly convert the RowNumber parameter to a string type, just like you're doing for LastTransferDt higher up in your code.

... EXTRACT.extract_num =" + (DT_WSTR,30)@[User::RowNumber]