Insert Multiple Row Data into a Variable
I have a recordset with one column and multiple rows that I would like to insert into the body of a Send Message task. So the body of the message could be as follows: "The following duplicate records were found in the table: RecordID1, RecordID2, RecordID3, etc..." My guess is I need to insert the recordset into a variable, but I'm not sure how to proceed.
October 27th, 2010 5:18pm

you can put a foreach loop and set enumerator as ado enumerator, then set your object type variable ( the variable which you filled data from recordset destination ), then set another variable in variable mappings tab ( this variable can be of datatype string), let's name this new variable as User::CurrentRecordID add another variable of type string, let's name this one as User::AllRecordIDs then put a script task inside foreach loop. set readonly variables with @[User::CurrentRecordID], and set readwriteVariabels as @[User::AllRecordIDs] then add this code in main() Dts.Variables["User::AllRecordIDs"].Value=Dts.Variables["User::AllRecordIDs"].Value.ToString()+", "+Dts.Variables["User::CurrentRecordID"].Value.ToString(); after foreach loop , you can use User::AllRecordIDs variable inside send message task.
Free Windows Admin Tool Kit Click here and download it now
October 27th, 2010 5:27pm

If you're not averse to third-party components, or HTML formatted mail, you can use the HTML Table Destination and Send HTML Mail Task. Or you can use a destination Script component in your data flow to stitch together your string and write it to a variable. Or you can use SSIS to push data to an SSRS report (or produce a temp table that an SSRS report would consume). Talk to me now on
October 27th, 2010 5:44pm

You can create a comma delimited string the via the dbo.fnSplitStringListXML user-defined function at the following link: Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM
Free Windows Admin Tool Kit Click here and download it now
October 29th, 2010 7:47am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics