question

siera_gld avatar image
siera_gld 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
ssisxmlvariables
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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?
0 Likes 0 ·

1 Answer

· Write an Answer
siera_gld avatar image
siera_gld answered
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]
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.