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

siera_gld gravatar image

siera_gld
1k 78 84 85

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

siera_gld gravatar image

siera_gld
1k 78 84 85

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x939
x150
x21

asked: Feb 12, 2011 at 01:52 PM

Seen: 1365 times

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