Syntax Issue with SSIS module

Remove below line from Query and create parameter "@temp_CareID" in SSIS Query box
DECLARE @temp_CareID TABLE (CareID varchar(14));

Hi Prakash,

I do apologize I have not experience in SSIS how would I create parameter "@temp_CareID" how would I do that could send me a article or screenshot.

Thank you.

In your Error snapshot, You can see various button along with "Parameters" button! I guess It's pretty simple to create it and use

Hi Prakash,

Below is the error when I try to add a parameter.

See attached.

Thank you.
Parameters-issue.PNG

I think the issue may be with @temp table.
Can you try the following version where I moved the @temp table into a subselect?

SELECT DISTINCT
	LEFT(r.NUMVAL,14) BluecareID,
	c.LASTNAME FamilyName,
	RTRIM(LTRIM((ISNULL(c.FIRSTNAME,'') + ' ' + ISNULL(c.MIDDLENAME,'')))) GivenNames,
	c.BIRTHDATE DateOfBrith,
	CASE ISNULL(c.GENDER,'') 
		WHEN '' THEN 'Unknown' 
		ELSE c.GENDER 
	END Gender,
    (ISNULL(c.CURR_ADDR1,'') + ', ' + ISNULL(c.CURR_ADDR2,'')) Street,
    c.CURR_CITY SuburbAndCity,
    c.CURR_POST Postcode
FROM CL_REFNOS r
	INNER JOIN CLIENTS c ON c.CLIENT_ID=r.CLIENT_ID AND ATTENDANCE<>'T'
	INNER JOIN CLTDEPT cd ON cd.CLTVISITOR_ID=c.CLIENT_ID AND cd.STATUS IN ('A','W','I')
	INNER JOIN ORDERS o ON o.CLIENT_ID=c.CLIENT_ID AND o.ENDDATE IS NULL
	INNER JOIN SCHGROUPCLTS sg ON sg.CLIENT_ID=c.CLIENT_ID AND sg.DEPT_ID=cd.DEPT_ID
	INNER JOIN SCHGROUPCLTSTATES sgs ON sgs.CLIENT_ID=c.CLIENT_ID AND sgs.STATUS IN ('A','W','X')  AND sgs.DEPT_ID=cd.DEPT_ID
WHERE r.NUMBER_ID='B0000000016' AND ISNULL(r.NUMVAL,'')<>''
	AND r.NUMVAL not IN (SELECT LEFT(rr.NUMVAL,14) BluecareID
						FROM CL_REFNOS rr
							INNER JOIN CLIENTS cc ON cc.CLIENT_ID=rr.CLIENT_ID
						WHERE NUMBER_ID='B0000000016' AND ISNULL(rr.NUMVAL,'')<>''
						GROUP BY LEFT(rr.NUMVAL,14)
						HAVING COUNT(1)>1)
Order by BluecareID

Select allOpen in new window

Hi Vitor,

Yes that works but I can't use that, because for some reason when the query is like that it takes over a 1hr to respond. With the @declare it takes only 9 seconds.

Thank you.

Your query uses DISTINCT and that's a performance killer.
Check if this helps to improve the query time:

SELECT 
	LEFT(r.NUMVAL,14) BluecareID,
	c.LASTNAME FamilyName,
	RTRIM(LTRIM((ISNULL(c.FIRSTNAME,'') + ' ' + ISNULL(c.MIDDLENAME,'')))) GivenNames,
	c.BIRTHDATE DateOfBrith,
	CASE ISNULL(c.GENDER,'') 
		WHEN '' THEN 'Unknown' 
		ELSE c.GENDER 
	END Gender,
    (ISNULL(c.CURR_ADDR1,'') + ', ' + ISNULL(c.CURR_ADDR2,'')) Street,
    c.CURR_CITY SuburbAndCity,
    c.CURR_POST Postcode
FROM CL_REFNOS r
	INNER JOIN CLIENTS c ON c.CLIENT_ID=r.CLIENT_ID AND ATTENDANCE<>'T'
	INNER JOIN CLTDEPT cd ON cd.CLTVISITOR_ID=c.CLIENT_ID AND cd.STATUS IN ('A','W','I')
	INNER JOIN ORDERS o ON o.CLIENT_ID=c.CLIENT_ID AND o.ENDDATE IS NULL
	INNER JOIN SCHGROUPCLTS sg ON sg.CLIENT_ID=c.CLIENT_ID AND sg.DEPT_ID=cd.DEPT_ID
	INNER JOIN SCHGROUPCLTSTATES sgs ON sgs.CLIENT_ID=c.CLIENT_ID AND sgs.STATUS IN ('A','W','X')  AND sgs.DEPT_ID=cd.DEPT_ID
WHERE r.NUMBER_ID='B0000000016' AND ISNULL(r.NUMVAL,'')<>''
	AND r.NUMVAL NOT IN (SELECT LEFT(rr.NUMVAL,14) BluecareID
						FROM CL_REFNOS rr
							INNER JOIN CLIENTS cc ON cc.CLIENT_ID=rr.CLIENT_ID
						WHERE NUMBER_ID='B0000000016' AND ISNULL(rr.NUMVAL,'')<>''
						GROUP BY LEFT(rr.NUMVAL,14)
						HAVING COUNT(1)>1)
GROUP BY
	LEFT(r.NUMVAL,14) BluecareID,
	c.LASTNAME FamilyName,
	RTRIM(LTRIM((ISNULL(c.FIRSTNAME,'') + ' ' + ISNULL(c.MIDDLENAME,'')))) GivenNames,
	c.BIRTHDATE DateOfBrith,
	CASE ISNULL(c.GENDER,'') 
		WHEN '' THEN 'Unknown' 
		ELSE c.GENDER 
	END Gender,
    (ISNULL(c.CURR_ADDR1,'') + ', ' + ISNULL(c.CURR_ADDR2,'')) Street,
    c.CURR_CITY SuburbAndCity,
    c.CURR_POST Postcode
ORDER BY BluecareID

Select allOpen in new window

Hi Vitor,

Odd syntax error:

Msg 102, Level 15, State 1, Line 27
Incorrect syntax near 'BluecareID'.
Msg 102, Level 15, State 1, Line 34
Incorrect syntax near 'Gender'.

Seems fine to me though checking now one sec.

Hi Vitor,

I checked the brackets and commas to the best of my ability and that query should work. The only hint the IDE is giving me is

Msg 102, Level 15, State 1, Line 34
Incorrect syntax near 'Gender'.

Expecting Conversation is the message beneath.

Thank you.

I checked the brackets and commas to the best of my ability and that query should work. The only hint the IDE is giving me is

My fault. I've copied the columns definitions and forgot to remove their aliases in the GROUP BY clause:

SELECT 
	LEFT(r.NUMVAL,14) BluecareID,
	c.LASTNAME FamilyName,
	RTRIM(LTRIM((ISNULL(c.FIRSTNAME,'') + ' ' + ISNULL(c.MIDDLENAME,'')))) GivenNames,
	c.BIRTHDATE DateOfBrith,
	CASE ISNULL(c.GENDER,'') 
		WHEN '' THEN 'Unknown' 
		ELSE c.GENDER 
	END Gender,
    (ISNULL(c.CURR_ADDR1,'') + ', ' + ISNULL(c.CURR_ADDR2,'')) Street,
    c.CURR_CITY SuburbAndCity,
    c.CURR_POST Postcode
FROM CL_REFNOS r
	INNER JOIN CLIENTS c ON c.CLIENT_ID=r.CLIENT_ID AND ATTENDANCE<>'T'
	INNER JOIN CLTDEPT cd ON cd.CLTVISITOR_ID=c.CLIENT_ID AND cd.STATUS IN ('A','W','I')
	INNER JOIN ORDERS o ON o.CLIENT_ID=c.CLIENT_ID AND o.ENDDATE IS NULL
	INNER JOIN SCHGROUPCLTS sg ON sg.CLIENT_ID=c.CLIENT_ID AND sg.DEPT_ID=cd.DEPT_ID
	INNER JOIN SCHGROUPCLTSTATES sgs ON sgs.CLIENT_ID=c.CLIENT_ID AND sgs.STATUS IN ('A','W','X')  AND sgs.DEPT_ID=cd.DEPT_ID
WHERE r.NUMBER_ID='B0000000016' AND ISNULL(r.NUMVAL,'')<>''
	AND r.NUMVAL NOT IN (SELECT LEFT(rr.NUMVAL,14) BluecareID
				FROM CL_REFNOS rr
					INNER JOIN CLIENTS cc ON cc.CLIENT_ID=rr.CLIENT_ID
				WHERE NUMBER_ID='B0000000016' AND ISNULL(rr.NUMVAL,'')<>''
				GROUP BY LEFT(rr.NUMVAL,14)
				HAVING COUNT(1)>1)
GROUP BY
	LEFT(r.NUMVAL,14),
	c.LASTNAME,
	RTRIM(LTRIM((ISNULL(c.FIRSTNAME,'') + ' ' + ISNULL(c.MIDDLENAME,'')))),
	c.BIRTHDATE,
	CASE ISNULL(c.GENDER,'') 
		WHEN '' THEN 'Unknown' 
		ELSE c.GENDER 
	END,
    (ISNULL(c.CURR_ADDR1,'') + ', ' + ISNULL(c.CURR_ADDR2,'')),
    c.CURR_CITY,
    c.CURR_POST
ORDER BY BluecareID

Select allOpen in new window

Hi Nakul,

Unfortunately, I can't use that due to the following error when I try to build the query. See attached pic.

Thank you though for the suggestion.
SSIS-Syntax-error-2.PNG

Hi Nakul/Vitor/Prakash

The network connection to SQL server just went down and isn't coming back up.

I will get back to this tomorrow.

Thank you for all help thus far.

Cheers.

With respect to the error referenced in Comment Id #42025560, the DROP table is to be placed in the "Execute SQL" task, not the "OLE DB source".

Hi Nakul,

I am getting the same error in the execute SQL task.

See screenshot.

Thankyou.

Zack, did you try my last script?

Hi Vitor,

I did it took around 28-37 minutes to run and a definite improvement. But this job needs to run every 5 minutes.

Did some research on the DISTINCT didn't know it was performance killer thank you the tip.

Zack, can you provide the query execution plan?
I guess there are some indexes missing and the query execution plan will confirm on that.

Hi Vitor,

I was going to post that execution plan here but the database we are querying owned by a 3rd party vendor, if we add any indexes we violate our support agreement.

Thank you.

Then you should at least let the vendor know about the performance issues so they might add the indexes.
Anyway, I've reviewed your query and improved the subselect. Please check if it's faster now:

SELECT 
	LEFT(r.NUMVAL,14) BluecareID,
	c.LASTNAME FamilyName,
	RTRIM(LTRIM((ISNULL(c.FIRSTNAME,'') + ' ' + ISNULL(c.MIDDLENAME,'')))) GivenNames,
	c.BIRTHDATE DateOfBrith,
	CASE ISNULL(c.GENDER,'') 
		WHEN '' THEN 'Unknown' 
		ELSE c.GENDER 
	END Gender,
    (ISNULL(c.CURR_ADDR1,'') + ', ' + ISNULL(c.CURR_ADDR2,'')) Street,
    c.CURR_CITY SuburbAndCity,
    c.CURR_POST Postcode
FROM CL_REFNOS r
	INNER JOIN CLIENTS c ON c.CLIENT_ID=r.CLIENT_ID AND ATTENDANCE<>'T'
	INNER JOIN CLTDEPT cd ON cd.CLTVISITOR_ID=c.CLIENT_ID AND cd.STATUS IN ('A','W','I')
	INNER JOIN ORDERS o ON o.CLIENT_ID=c.CLIENT_ID AND o.ENDDATE IS NULL
	INNER JOIN SCHGROUPCLTS sg ON sg.CLIENT_ID=c.CLIENT_ID AND sg.DEPT_ID=cd.DEPT_ID
	INNER JOIN SCHGROUPCLTSTATES sgs ON sgs.CLIENT_ID=c.CLIENT_ID AND sgs.STATUS IN ('A','W','X')  AND sgs.DEPT_ID=cd.DEPT_ID
WHERE r.NUMBER_ID='B0000000016' AND 
	AND r.NUMVAL NOT IN (SELECT LEFT(rr.NUMVAL,14) BluecareID
				FROM CL_REFNOS rr
				WHERE rr.NUMBER_ID=r.NUMBER_ID AND rr.CLIENT_ID=r.CLIENT_ID
					 AND r.NUMVAL IS NOT NULL AND r.NUMVAL <>''
				GROUP BY LEFT(rr.NUMVAL,14)
				HAVING COUNT(1)>1)
GROUP BY
	LEFT(r.NUMVAL,14),
	c.LASTNAME,
	RTRIM(LTRIM((ISNULL(c.FIRSTNAME,'') + ' ' + ISNULL(c.MIDDLENAME,'')))),
	c.BIRTHDATE,
	CASE ISNULL(c.GENDER,'') 
		WHEN '' THEN 'Unknown' 
		ELSE c.GENDER 
	END,
    (ISNULL(c.CURR_ADDR1,'') + ', ' + ISNULL(c.CURR_ADDR2,'')),
    c.CURR_CITY,
    c.CURR_POST
ORDER BY BluecareID

Select allOpen in new window

Hi Vitor,

I have already let the vendor know this has been a long-standing issue, their response was basically; Any queries that are not written by the company we are obligated to troubleshoot for performance issues :(.

I am trying your query now.

Thank you.

Hi Vitor/Nakul,

The query is faster 12 mins but still not under the 5-minute mark required.

Thank you for all your assistance, if don't find a solution myself on the internet in the next day or so.

I will be closing out this question and awarding points regardless in a day or so. Yourself and Nakul have already given me solutions I just got to try to find a way to make them work.

Hi Nakul,

Any ideas for the earlier post I made:

Hi Nakul,

I am getting the same error in the execute SQL task.

See screenshot.

Thankyou.

Hi Nakul,

One thought, the SSIS parser doesn't implement a full-blown Transact-SQL ruleset to my knowledge. Should I just try to implement this package and give it a go?

Thank ou.

That got it 8 seconds thank you so much again Vitor and for all your help on this question and in general.