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"

Hadi · Accepted Answer · 2017-09-14 22:20:27Z

2

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

edited Sep 14, 2017 at 22:20

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

answered Sep 14, 2017 at 19:25

KeithL

5,6193 gold badges23 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

emre güzel Over a year ago

Execute sql task statemnt query : DELETE [etl].[ErrorMessage] where created_time <= ( DATEADD(SECOND,-1, CONVERT(datetime, CONVERT(date, DATEADD(DAY, -89, GETDATE())))) ) and I want to use this variable instead of -89. How can I do this?

emre güzel Over a year ago

Where does sql keep the errors that occur when the SSIS package is run?

KeithL Over a year ago

I don't think it is inherent, but you can turn on logging. i prefer to log to the file system (mostly because it's quicker to access in PROD.

emre güzel Over a year ago

Can SSIS te be deployed with a task?

Collectives™ on Stack Overflow

How to use variable with sql query in ssis te variable

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related