x

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
more ▼

asked Feb 12, 2011 at 01:52 PM in Default

avatar image

siera_gld
1k 82 88 93

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?

Feb 13, 2011 at 07:34 PM KenJ
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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]

more ▼

answered Feb 13, 2011 at 09:37 PM

avatar image

siera_gld
1k 82 88 93

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1152
x189
x32

asked: Feb 12, 2011 at 01:52 PM

Seen: 1566 times

Last Updated: Feb 12, 2011 at 01:52 PM

Copyright 2016 Redgate Software. Privacy Policy