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.http://www.rad.pasfu.com
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:
http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/
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