1

I am getting the following error statement when running a SSIS package. My statement includes

FROM [tablename] WITH(nolock)

Whenever I parse, it says it’s valid but it fails in the Pre-execute stage. Does anyone know what I am doing wrong?

Messages
Error 0xc0202009: Data Flow Task 1: 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: “Case expressions may only be nested to level 10.”.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80040E14 Description: “Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.”.
An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80040E14 Description: “Statement(s) could not be prepared.”.
(SQL Server Import and Export Wizard)

4 Spice ups
2

I’ve not had any of those errors. In any case, you would want to start by looking at the first error thrown, not the last.

Do you have more than 9 levels of nested case statements? I would guess you don’t. Presuming that is correct, I would expect that there’s a statement that’s not properly closed causing the cascade of errors.

3

I don’t. The query runs just fine for me outside of SSIS.

4

Post your code (sanitized as needed) so we can have a look.

2 Spice ups
5

I would triple check that the code in SSIS is actually the code you’re running. More than once when developing our SSIS stuff I found I didn’t have exactly the code I’d expected pasted into the SSIS package.

There’s some reason it thinks there’s a nested case statement. In my experience I’ve not had the SQL server tell me something that was wrong. Misleading, yes. Wrong, no.

1 Spice up
6

This could also be due to the “linked server problem” pointed out by Aaron Bertrand and Paul White where a CASE statement gets re-written by the parser into nested form when sending it to a linked server.

1 Spice up
7 
SELECT schoolid as [SchoolID],
						(CASE 
	when schoolid = '1' then 'School Name 1' 
	when schoolid = '2' then 'School Name 2' 
	when schoolid = '3' then 'School Name 3' 
	when schoolid = '4' then 'School Name 4' 
	when schoolid = '5' then 'School Name 5' 
	when schoolid = '6' then 'School Name 6'  
	when schoolid = '7' then 'School Name 7' 
	when schoolid = '8' then 'School Name 8'
	when schoolid = '9' then 'School Name 9' 
	when schoolid = '10' then 'School Name 10' 
	when schoolid = '11' then 'School Name 11' 
	when schoolid = '12' then 'School Name 12' 
	when schoolid = '13' then 'School Name 13' 
	when schoolid = '14' then 'School Name 14' 
	when schoolid = '15' then 'School Name 15' 
	when schoolid = '16' then 'School Name 16'  
	when schoolid = '17' then 'School Name 17' 
						END) AS [School_name],

	number as [School_number],
	districtid as [State_id],
        principalname as Principal,
	principalEmail as [Principal_email],
	address as [School_address],
	city as [School_city],
	state as [School_state],
	zip as [School_zip],
	phone as [School_phone]

FROM [ip address].database.dbo.table with(nolock)
WHERE schoolID in ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17')
ORDER BY schoolID

Here is the code… I removed my case statement and was still having issues with it…

8

I could easily see that CASE getting parsed to a nested one. Could be something similar with the IN clause.

The table doesn’t have the school name? Seems odd.

9

Removing the case statement didn’t resolve my issue. I was still getting error: “Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.”.

What’s confusing to me is I set up about 3 others with the same with(nolock) and had no issues.

10

It does have school names but I had to change them to match my import… The place I am importing the files to do not have our correct school names.

11

How would I need to re-write to correct this?

12

Okay, lots of questions.

First, is NOLOCK absolutely needed? There are serious side-effects of using it . DON’T do it just to speed up the query. If it is needed I’d suggest explicitly setting the isolation level for the transaction instead.

As far as school names go, the first thought is to correct the data on the destination so the correct names are available. Or is that the point of the exercise?

David, any other thoughts?

@dsentelle

2 Spice ups
13

I’ve personally never used a FROM clause with [ServerIP].DBName.dbo.TableName, I typically skip the DB name and even the dbo references. I would remove the With (nolock) hint, and the case statement… I’ve not worked with linked servers. Based on Larry’s comment about the “linked server problem”, and the fact that you’re explicitly stating the server’s IP… I think it will work without that and the case statement.

RGee you said,

" Removing the case statement didn’t resolve my issue. I was still getting error: "Incorrect syntax near the keyword ‘with’. "

I think you should move on to figuring out why the “with” hint is throwing an error.

In any case, pun not originally intended, since removing the case statement fixed that error, I would change that case statement. Instead of case, why not just do this…

'School Name ' + schoolid as [School_name],

@ryangee

1 Spice up