Hi All

I have an unusual issue and wondered if someone could assist. have a table which contains a list of columns which i need to feed into the query. I have written some dynamic SQL as shown:

Declare @KeyString varchar(100)

Declare @SQL1 nvarchar(max)

Set @KeyString=(Select STUFF((SELECT '+' + Attr FROM ave_control.ETL.Source inner JOIN [DM_AVE2].[Reference].[CRKAttr] on client = sourcename COLLATE DATABASE_DEFAULT where Client = 'Brampton' Order by AttrOrder FOR XML PATH('')),1,1,'') AS KeyString)
;
Set @SQL1 ='WITH CLAIMDATA AS(
				Select 
				DB_NAME() AS DBName,
				Convert(Varchar,UK.KMPONO) + ''/'' + Convert(Varchar,UK.KMPOSQ) + ''/'' + Convert(Varchar,UK.KMUGYR) + ''/'' + Convert(Varchar,UK.KMDANO) AS [POLICYREF],
				Convert(Varchar,UK.KMPONO) + ''/'' + Convert(Varchar,UK.KMPOSQ) + ''/'' + Convert(Varchar,UK.KMUGYR) + ''/'' + Convert(Varchar,UK.KMDANO) + ''/'' + CONVERT(VarChar,UK.KMCMSQ) AS [CLAIMREF],
				left(19000000+KMLODT,4) as [LOSSYEAR],
				UK.KMLOCD AS [LOSSCODE],
				CASE WHEN KMLOCD = '''' then COALESCE(UC.LOLODS,'''') ELSE UC.LOLODS END AS LOSSDESCRIPTION,
				UK.KMEVCD AS [EVENTCODE],
				KMCMDS AS [CLAIMDESCRIPTION],
				CASE WHEN LEFT(KMCMDS,1) = ''@'' THEN ''Y'' ELSE ''N'' END AS [STATICRESERVEREVIEW],
				CASE WHEN UCE.EVEVDS IS NULL then COALESCE(UCE.EVEVDS,'''') ELSE UCE.EVEVDS END AS [EVENTCODEDESCRIPTION]
				FROM UKKMREP UK
				LEFT JOIN UCLOREP UC ON UK.KMLOCD = UC.LOLOCD
				LEFT JOIN UCEVREP UCE ON UK.KMEVCD = UCE.EVEVCD),
	COGDATA AS(		
				SELECT TOP 100 PERCENT
				CONVERT(Varchar,POLICY)+''/''+CONVERT(Varchar,CO.SEQ)+''/''+CONVERT(Varchar,CO.[YEAR])+''/''+CONVERT(Varchar,CO.[DEC])+''/''+CONVERT(Varchar,CO.CLAIM) AS [CLAIMREF],
				[Type],
				SUM(isnull(CO.KCR,0)+isnull(CO.ACR,0)+isnull(CO.[OCR],0)+isnull(CO.[EXP],0)+isnull(Co.[LOC],0)) AS OLR
				FROM COG626A CO
				Group by CONVERT(Varchar,POLICY)+''/''+CONVERT(Varchar,CO.SEQ)+''/''+CONVERT(Varchar,CO.[YEAR])+''/''+CONVERT(Varchar,CO.[DEC])+''/''+CONVERT(Varchar,CO.CLAIM), [Type]
				ORDER BY CLAIMREF
				
				),
	CLAIMRCData AS(
					Select distinct
					DB_NAME() As DBNAME,
					Convert(Varchar,UK.KMPONO) + ''/'' + Convert(Varchar,UK.KMPOSQ) + ''/'' + Convert(Varchar,UK.KMUGYR) + ''/'' + Convert(Varchar,UK.KMDANO) + ''/'' + CONVERT(VarChar,UK.KMCMSQ) AS [CLAIMREF],
					Convert(varchar,isnull(pocscd,'''')) as POCSCD,
					Convert(varchar,isnull(pomkrf,'''')) AS PMKRF,
					Convert(varchar,isnull(poplcd,'''')) AS POPLCD,
					Convert(varchar,isnull(poqdfg,'''')) AS POQDFG,
					Convert(varchar,isnull(pougty,'''')) AS POUGTY,
					Convert(varchar,isnull(pogucd,'''')) AS POGUCD,
					Convert(varchar,isnull(kmbhcd,'''')) AS KMBHCD,
					Convert(varchar,isnull(kmcccd,'''')) AS KMCCCD,
					Convert(varchar,isnull(kmdpdv,'''')) AS KMDPDV,
					Convert(varchar,isnull(kmevcd,'''')) AS KMEVCD,
					Convert(varchar,isnull(kmimcd,'''')) AS KMIMCD,
					Convert(varchar,isnull(kmiycd,'''')) AS KMIYCD,
					Convert(varchar,isnull(kmlocd,'''')) AS KMLOCD
					from UPPOREP UP Left join UKKMREP UK on UP.POPONO = UK.KMPONO AND UP.POPOSQ = KMPOSQ AND UP.POUGYR = UK.kmugyr AND UP.PODANO = UK.KMDANO)
			Select distinct 
				C1.DBName, 
				C1.POLICYREF, 
				C1.CLAIMREF,
				C1.LossDescription,
				C1.LOSSCODE,
				C1.LOSSYEAR,
				C1.EventCode,
				C1.ClaimDescription,
				C1.StaticReserveReview,
				C1.EventCodeDescription,' 
				+ @KeyString + ' AS ClaimKey,
				Round(CG1.OLR,2) as [OLR],
				CASE WHEN CG1.OLR <> 0 THEN ''OPEN'' ELSE ''CLOSED'' END AS [ClaimStatus] 
				FROM CLAIMDATA C1
				LEFT JOIN COGDATA CG1 on C1.CLAIMREF = CG1.CLAIMREF
				LEFT JOIN CLAIMRCData CR on C1.CLAIMREF = CR.CLAIMREF
		UNION
				Select distinct
				C1.DBName,
				C1.POLICYREF,
				SUBSTRING(CG1.ClaimRef,1,Len(CG1.ClaimRef)-2) + ''/0'',
				''''					AS LossDescription,
				''''					AS LossCode,
				1912				AS LossYear,
				''''					AS EventCode,
				''''					AS ClaimDescription,
				''''					AS StaticReserveReview,
				''''					AS EventCodeDescription,'
				+ @KeyString + '	AS ClaimKey,
				0					AS OLR,
				''CLOSED''			AS CLAIMSTATUS
				From CLAIMDATA C1
				INNER JOIN COGDATA CG1 on C1.CLAIMREF = CG1.CLAIMREF
				LEFT JOIN CLAIMRCData CR on C1.CLAIMREF = CR.CLAIMREF
				Order by C1.POLICYREF'

			Select @SQL1

I always want to output @Keystring using a DFT but depending on the client the Keystring is set to a different group of columns and selects a different set of columns from the CLAIMRCData loop.

I tried inserting it as dynamic SQL as a variable but cannot get it to parse - does anyone know how to achieve this?

Here is the output from the Select @SQL1 which always returns the value I want and if i execute this and change the clientname it successfully returns the right claimkey each time - i.e. another client has 3 columns and these are successfully parsed - i just dont know how to get it into SSIS. Output as shown:

WITH CLAIMDATA AS(
				Select 
				DB_NAME() AS DBName,
				Convert(Varchar,UK.KMPONO) + '/' + Convert(Varchar,UK.KMPOSQ) + '/' + Convert(Varchar,UK.KMUGYR) + '/' + Convert(Varchar,UK.KMDANO) AS [POLICYREF],
				Convert(Varchar,UK.KMPONO) + '/' + Convert(Varchar,UK.KMPOSQ) + '/' + Convert(Varchar,UK.KMUGYR) + '/' + Convert(Varchar,UK.KMDANO) + '/' + CONVERT(VarChar,UK.KMCMSQ) AS [CLAIMREF],
				left(19000000+KMLODT,4) as [LOSSYEAR],
				UK.KMLOCD AS [LOSSCODE],
				CASE WHEN KMLOCD = '' then COALESCE(UC.LOLODS,'') ELSE UC.LOLODS END AS LOSSDESCRIPTION,
				UK.KMEVCD AS [EVENTCODE],
				KMCMDS AS [CLAIMDESCRIPTION],
				CASE WHEN LEFT(KMCMDS,1) = '@' THEN 'Y' ELSE 'N' END AS [STATICRESERVEREVIEW],
				CASE WHEN UCE.EVEVDS IS NULL then COALESCE(UCE.EVEVDS,'') ELSE UCE.EVEVDS END AS [EVENTCODEDESCRIPTION]
				FROM UKKMREP UK
				LEFT JOIN UCLOREP UC ON UK.KMLOCD = UC.LOLOCD
				LEFT JOIN UCEVREP UCE ON UK.KMEVCD = UCE.EVEVCD),
	COGDATA AS(		
				SELECT TOP 100 PERCENT
				CONVERT(Varchar,POLICY)+'/'+CONVERT(Varchar,CO.SEQ)+'/'+CONVERT(Varchar,CO.[YEAR])+'/'+CONVERT(Varchar,CO.[DEC])+'/'+CONVERT(Varchar,CO.CLAIM) AS [CLAIMREF],
				[Type],
				SUM(isnull(CO.KCR,0)+isnull(CO.ACR,0)+isnull(CO.[OCR],0)+isnull(CO.[EXP],0)+isnull(Co.[LOC],0)) AS OLR
				FROM COG626A CO
				Group by CONVERT(Varchar,POLICY)+'/'+CONVERT(Varchar,CO.SEQ)+'/'+CONVERT(Varchar,CO.[YEAR])+'/'+CONVERT(Varchar,CO.[DEC])+'/'+CONVERT(Varchar,CO.CLAIM), [Type]
				ORDER BY CLAIMREF
				
				),
	CLAIMRCData AS(
					Select distinct
					DB_NAME() As DBNAME,
					Convert(Varchar,UK.KMPONO) + '/' + Convert(Varchar,UK.KMPOSQ) + '/' + Convert(Varchar,UK.KMUGYR) + '/' + Convert(Varchar,UK.KMDANO) + '/' + CONVERT(VarChar,UK.KMCMSQ) AS [CLAIMREF],
					Convert(varchar,isnull(pocscd,'')) as POCSCD,
					Convert(varchar,isnull(pomkrf,'')) AS PMKRF,
					Convert(varchar,isnull(poplcd,'')) AS POPLCD,
					Convert(varchar,isnull(poqdfg,'')) AS POQDFG,
					Convert(varchar,isnull(pougty,'')) AS POUGTY,
					Convert(varchar,isnull(pogucd,'')) AS POGUCD,
					Convert(varchar,isnull(kmbhcd,'')) AS KMBHCD,
					Convert(varchar,isnull(kmcccd,'')) AS KMCCCD,
					Convert(varchar,isnull(kmdpdv,'')) AS KMDPDV,
					Convert(varchar,isnull(kmevcd,'')) AS KMEVCD,
					Convert(varchar,isnull(kmimcd,'')) AS KMIMCD,
					Convert(varchar,isnull(kmiycd,'')) AS KMIYCD,
					Convert(varchar,isnull(kmlocd,'')) AS KMLOCD
					from UPPOREP UP Left join UKKMREP UK on UP.POPONO = UK.KMPONO AND UP.POPOSQ = KMPOSQ AND UP.POUGYR = UK.kmugyr AND UP.PODANO = UK.KMDANO)
			Select distinct 
				C1.DBName, 
				C1.POLICYREF, 
				C1.CLAIMREF,
				C1.LossDescription,
				C1.LOSSCODE,
				C1.LOSSYEAR,
				C1.EventCode,
				C1.ClaimDescription,
				C1.StaticReserveReview,
				C1.EventCodeDescription,pogucd+pougty AS ClaimKey,
				Round(CG1.OLR,2) as [OLR],
				CASE WHEN CG1.OLR <> 0 THEN 'OPEN' ELSE 'CLOSED' END AS [ClaimStatus] 
				FROM CLAIMDATA C1
				LEFT JOIN COGDATA CG1 on C1.CLAIMREF = CG1.CLAIMREF
				LEFT JOIN CLAIMRCData CR on C1.CLAIMREF = CR.CLAIMREF
		UNION
				Select distinct
				C1.DBName,
				C1.POLICYREF,
				SUBSTRING(CG1.ClaimRef,1,Len(CG1.ClaimRef)-2) + '/0',
				''					AS LossDescription,
				''					AS LossCode,
				1912				AS LossYear,
				''					AS EventCode,
				''					AS ClaimDescription,
				''					AS StaticReserveReview,
				''					AS EventCodeDescription,pogucd+pougty	AS ClaimKey,
				0					AS OLR,
				'CLOSED'			AS CLAIMSTATUS
				From CLAIMDATA C1
				INNER JOIN COGDATA CG1 on C1.CLAIMREF = CG1.CLAIMREF
				LEFT JOIN CLAIMRCData CR on C1.CLAIMREF = CR.CLAIMREF
				Order by C1.POLICYREF

any help would be greatly appreciated - oh version is SQL 2014 SSIS

Thanks

James

When you say not able to parse, can you give the expression itself?

And in what SSIS component do you run this query?

And what error do you get?

My cursory look (too much code to digest frankly) says you need to wrap it into a stored procedure.

You'll want to use this with an Execute SQL Task.

Create the object, attach it to your connection, and drop your DSQL in there, setting the output to a variable (SELECT @x = ....).

Once you've done that, goto the parameter mapping tab, and assign the value of your parameter to a SSIS parameter and you're away.

You can then use that variable in a DFT source as a query.

• Proposed as answer by Katherine XiongMicrosoft contingent staff, Moderator 20 hours 23 minutes ago

Hi James,

The standard Data Flow Task has metadata defined statically at design time. I would recommend you check the commercial COZYROC Data Flow Task Plus. It is an extension of the standard Data Flow Task which supports dynamic metadata at runtime. You can define any list of columns in your source component and the dynamic data flow task will handle it. No programmings skills are required.