asked
SSIS Variables with xml
I want to use an xml path and assign the results to variables within an ssis package. I keep getting errors that it will not assign the variable. I am converting a flat table wihch has email addresses in one column to a more normalized structure (header/detail) so in my ssis routine i need to keep in place for every one customer email a list of recipients. the table works fine - but not normalized and I need to clean this up. SELECT HDR.CUST_NUM ,HDR.CUST_NAME ,HDR.CNTC ,STUFF((SELECT ';'+ dtl.EM_ADDRESS FROM DEV_USR.dbo.T_OS_WEEKLY_RPT_DIST AS dtl WHERE dtl.EM_TYP = 'TO' AND dtl.CUST_NUM = HDR.CUST_NUM FOR XML PATH('')),1,1,'') as [TO] ,STUFF((SELECT ';'+ dtl.EM_ADDRESS FROM DEV_USR.dbo.T_OS_WEEKLY_RPT_DIST AS dtl WHERE dtl.EM_TYP = 'CC' AND dtl.CUST_NUM = HDR.CUST_NUM FOR XML PATH('')),1,1,'') as [CC] ,STUFF((SELECT ';'+ dtl.EM_ADDRESS FROM DEV_USR.dbo.T_OS_WEEKLY_RPT_DIST AS dtl WHERE dtl.EM_TYP = 'BCC' AND dtl.CUST_NUM = HDR.CUST_NUM FOR XML PATH('')),1,1,'') as [BCC] FROM DEV_USR.dbo.T_OS_WEEKLY_DIST_HDR hdr GROUP BY HDR.CUST_NUM ,HDR.CUST_NAME ,HDR.CNTC
Can you include the actual error message? Are you assigning the variables within the scope of a foreach container, or outside of one where you might end up trying to put multiple rows into the variable? Do the data types of the SSIS variables match the data types of the query output columns?
I got it - you have to convert to varchar Convert(varchar(1000), STUFF((SELECT ';'+ dtl.EM_ADDRESS FROM DEV_USR.dbo.T_OS_WEEKLY_RPT_DIST AS dtl WHERE dtl.EM_TYP = 'BCC' AND dtl.CUST_NUM = HDR.CUST_NUM FOR XML PATH('')),1,1,''))as [BCC]
