SSIS sql query error

SSIS and Import/Export
1

WITH CTE AS ( SELECT
CONVERT(date, CONVERT(VARCHAR,CURRENT_TIMESTAMP,23), 111) as data_date,
FROM [db_bis].[staging].[stg_iwmf_xls_ecn_log_epc1] WITH (NOLOCK)
),
CTE2 AS
( SELECT
*,
CASE
WHEN [client_approved_or_rejected] IS NOT NULL and [client_approved_or_rejected] IN ('In Discussion', 'Submitted') THEN datediff(day, [date_raised],data_date)
ELSE NULL
END as days_outstanding
FROM
CTE)

When I run this query in the SSIS sql command window, it gives me the error.

TITLE: Microsoft Visual Studio

Exception from HRESULT: 0xC0202009
Error at DFT - Insert into stg_iwmf_summary_ecn_log_epc1 [OLE_SRC - Get data from stg_iwmf_xls_ecn_log_epc1 [78]]: 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: "Incorrect syntax near ')'.".

what could be wrong? Any help

2

Try to run the query in SSMS, that will be easy to fix the syntax error.

3

You habe 2 cte but not final query after the last cte

4

You don't need the second CTE if the query in the second CTE outputs the data you want. There are several more issues. The first - and primary issue - is that the first CTE is only returning a single column and therefore any additional columns referenced in the second CTE are not going to be available.

The next issue is this statement:

CONVERT(date, CONVERT(VARCHAR,CURRENT_TIMESTAMP,23), 111) as data_date,

Just cast/convert to a date:

CONVERT(date, CURRENT_TIMESTAMP) as data_date,
--CAST(CURRENT_TIMESTAMP AS date)

The next issue is the CASE expression - no need to check that the value is not null - if the value is null then it wouldn't be in the list and the THEN statement wouldn't be executed.

CASE
WHEN [client_approved_or_rejected] IS NOT NULL and [client_approved_or_rejected] IN ('In Discussion', 'Submitted') THEN datediff(day, [date_raised],data_date)
ELSE NULL
END as days_outstanding

to just this

CASE
WHEN [client_approved_or_rejected] IN ('In Discussion', 'Submitted') THEN datediff(day, [date_raised],data_date)
ELSE NULL
END as days_outstanding

And now we can simplify to this:

SELECT
*,
CASE
WHEN [client_approved_or_rejected] IN ('In Discussion', 'Submitted') THEN datediff(day, [date_raised], current_timestamp)
ELSE NULL
END as days_outstanding
FROM [db_bis].[staging].[stg_iwmf_xls_ecn_log_epc1] WITH (NOLOCK)

We can ignore the cast/convert to a date data type because the datediff will return the number of days difference regardless of the time component.

5

thanks for the detailed explanation. .Let me try to work on it and will get back to you.