CODEWITHSUNDEEP
sql-serverssissql-server-2012ssis-2012
OverflowingTheGlass
OverflowingTheGlass

Reputation: 2434

SQL Server Job Expression for Variable Failed Evaluation

I have an SSIS package that runs without errors locally. It is then deployed to the catalog and run in a SQL Agent job. Functionally, the job runs correctly and all data is where it should be, but it always kicks out this error:

The expression for variable "TextMaxDate" failed evaluation. There was an error in the expression.

I have not been able to find a more meaningful message. The SSIS package has three variables...

  1. TextMaxDate

    • Data type = DateTime
    • Expression = @[User::TextMaxDate]

  2. TextSQL_Content

    • Data type = String
    • Expression = "SELECT CREATED_DATE, ID, TEXT FROM SRC_SCHEMA.SRC_TABLE_1 WHERE CREATED_DATE BETWEEN '" + (DT_STR, 12, 1252)(DT_DBDATE) DATEADD("day",1,(DT_DBDATE)@[User::TextMaxDate]) + "' AND '" + (DT_STR, 25, 1252) (DT_DBDATE) DATEADD( "day", - 1 , (DT_DBDATE) GETDATE() ) + "'"

  3. TextSQL_NoContent

    • Data type = String
    • Expression = "SELECT ID, CREATED, CREATED_BY, TEXT FROM SRC_SCHEMA.SRC_TABLE_1 WHERE CREATED_DATE BETWEEN '" + (DT_STR, 12, 1252)(DT_DBDATE) DATEADD("day",1,(DT_DBDATE)@[User::TextMaxDate]) + "' AND '" + (DT_STR, 25, 1252) (DT_DBDATE) DATEADD( "day", - 1 , (DT_DBDATE) GETDATE() ) + "'"

and follows this flow...

  1. Execute SQL Task

    • SQLStatement is set to select max(cast(created_date as date)) MaxDate from DEST_SCHEMA.table_1.
    • Result Set sets Result Name to MaxDate and Variable Name to User::TextMaxDate

  2. Data Flow Task (Oracle Attunity Driver)

    • Under Expressions...[Oracle Source].[SqlCommand] set to @[User::TextSQL_NoContent]
    • Loads data into DEST_SCHEMA.TABLE_1, but doesn't map the TEXT column so that it is null for each inserted record.

  3. Data Flow Task (OLE DB Source Editor)

    • Data Access Mode set to SQL command from variable
    • Variable Name set to User::TextSQL_Content
    • Loads data into DEST_SCHEMA.TABLE_2

  4. Execute SQL Task

    • SQLStatement set to UPDATE DEST_SCHEMA.TABLE_1 SET DEST_SCHEMA.TABLE_1.TEXT b.TEXT from DEST_SCHEMA.TABLE_1 a join DEST_SCHEMA.TABLE_2 b on a.id = b.id where a.TEXT IS NULL

Why is this error occurring?

Upvotes: 2

Views: 703

Answers (1)

Hadi
Hadi

Reputation: 37348

Why writing an expression to set @[User::TextMaxDate] = @[User::TextMaxDate] !!

Just remove the expression and set EvaluateAsExpression to False. Also make sure that 

select max(cast(created_date as date)) MaxDate from DEST_SCHEMA.table_1

is returning a value.

Upvotes: 1

Related Questions