How to use variable with sql query in ssis te variable

Question

VARIABLE VALUE:-89


ERROR MESSAGE

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at DFT_SSISDB_To_DWH [OLEDB_SRC_executions & event_messages [58]]: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E14  Description: "Statement(s) could not be prepared.".
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80040E14  Description: "Must declare the scalar variable "@".".
Error at DFT_SSISDB_To_DWH [SSIS.Pipeline]: "OLEDB_SRC_executions & event_messages" failed validation and returned validation status "VS_ISBROKEN".
Error at DFT_SSISDB_To_DWH [SSIS.Pipeline]: One or more component failed validation.
Error at DFT_SSISDB_To_DWH: There were errors during task validation.
 (Microsoft.DataTransformationServices.VsIntegration)

VARIABLE QUERY

"SELECT Distinct
    em.event_message_id
    --,em.operation_id
    ,ex.execution_id
    ,em.package_name
    ,CASE
    WHEN em.message_source_type = 10 THEN '10 - Entry APIs, such as T-SQL and CLR Stored procedures'
    WHEN em.message_source_type = 20 THEN '20 - External process used to run package (ISServerExec.exe)'
    WHEN em.message_source_type = 30 THEN '30 - Package-level objects'
    WHEN em.message_source_type = 40 THEN '40 - Control Flow tasks'
    WHEN em.message_source_type = 50 THEN '50 - Control Flow containers'
    WHEN em.message_source_type = 60 THEN '60 - Data Flow task'
    END as message_source_type_description
    ,em.message
    --,em.subcomponent_name
    --,em.package_path
    ,em.message_time
    ,ex.created_time
    ,ex.server_name
    FROM [catalog].[event_messages] em WITH (NOLOCK)
    INNER JOIN [catalog].[executions] ex WITH (NOLOCK) ON ex.execution_id = em.operation_id
    WHERE em.message_type = 120 --Selects Errors Only. Add 110 for Warnings
    AND ex.created_time >=  DATEADD(SECOND,-1,CONVERT(datetime, CONVERT(date,  DATEADD(DAY, **@[User::LastDayTime]**, GETDATE()))))
    --AND ex.execution_id = 628"

KeithL KeithL · Accepted Answer · 2017-09-14T19:25:21

Replace this @[User::LastDayTime]

with

"+ @[User::LastDayTime] + "

It will use your variable that way.

Merging your comment into my answer:

set variable @sql =

"SELECT Distinct
    em.event_message_id
    --,em.operation_id
    ,ex.execution_id
    ,em.package_name
    ,CASE
    WHEN em.message_source_type = 10 THEN '10 - Entry APIs, such as T-SQL and CLR Stored procedures'
    WHEN em.message_source_type = 20 THEN '20 - External process used to run package (ISServerExec.exe)'
    WHEN em.message_source_type = 30 THEN '30 - Package-level objects'
    WHEN em.message_source_type = 40 THEN '40 - Control Flow tasks'
    WHEN em.message_source_type = 50 THEN '50 - Control Flow containers'
    WHEN em.message_source_type = 60 THEN '60 - Data Flow task'
    END as message_source_type_description
    ,em.message
    --,em.subcomponent_name
    --,em.package_path
    ,em.message_time
    ,ex.created_time
    ,ex.server_name
    FROM [catalog].[event_messages] em WITH (NOLOCK)
    INNER JOIN [catalog].[executions] ex WITH (NOLOCK) ON ex.execution_id = em.operation_id
    WHERE em.message_type = 120 --Selects Errors Only. Add 110 for Warnings
    AND ex.created_time >=  DATEADD(SECOND,-1,CONVERT(datetime, CONVERT(date,  DATEADD(DAY, " + @[User::LastDayTime] + ", GETDATE()))))
    --AND ex.execution_id = 628"

execute variable @sql in execute sql task

same thing here:

@sql =
"DELETE [etl].[ErrorMessage] where created_time <= ( DATEADD(SECOND,-1, 
CONVERT(datetime, CONVERT(date, DATEADD(DAY, "+ @variable +", GETDATE())))))"

Now execute @sql

How to use variable with sql query in ssis te variable

1 Answers