variables

Question

variables

justforgroups2004

Old Hand

Points: 315
More actions
December 22, 2011 at 11:48 am

#96688

If I am not posting to the right place, please accept my sincere apologies, and move to the appropriate location. Thank you!
=============================
SQL7 and SQL2005 - can use either one of them.
I have a simple Query1 that extracts information from Table1. Would like to run this query about 40 times with different parameters passed from Table2 (RecipientNumber field), and then email results in Excel format to 40 different addresses passed from the same Table2 (RecipientEmailAddress field). Table2 consists of two columns: RecipientNumber - used for the Query1, and RecipientEmailAddress - used for the email routine.
I could extract with DTS (hard coding RecipientNumber one by one), and then email with xp_sendmail (hard coding RecipientEmailAddress one by one), but Table2 is frequently updated. 🙁
Is there something that would allow reuse of the same DTS, while passing RecipientNumber from Table2 dynamically (?looping?)? And then, maybe "looping" through all RecipientEmailAddress entries (matching Excel file name by RecipientNumber) with xp_sendmail to send out attachments? .. Or any other suggestions are greatly appreciated! 🙂

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

It's been a while since I used DTS. In SSIS, what you're asking about is trivially easy.

You use an SQL task to query table 2, then a For Each Next loop to step through the dataset from that query, outputing an e-mailing a spreadsheet for each row in table 2.

Dead easy. Half an hour to build, maybe less.

Does DTS have that kind of looping ability? I haven't used it in about 6 or 7 years, and just don't remember.

Or do you have access to use SSIS instead of DTS?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

justforgroups2004 Old Hand Points: 315 More actions · Answer 2

Thank you for reply, GSquared! 🙂

I have two servers 7 and 2005. Never worked with SSIS, but just looked it up, and it appears to be a part of SQL 2005 ("First released with Microsoft SQL Server 2005, SSIS replaced Data Transformation Services" -wiki).

Then, I guess you found me some holiday reading. Will get a book on SSIS, and dive in.

Just to confirm: I need to run Query1 (pull from Table1) for every instance of RecipientNumber in Table2. And then email Excel format of the output to every instance of RecipientEmailAddress in the Table2, matching the attachments by RecipientNumber.

So, you think SSIS will do that?

If you have any other valuable points/steps/etc, please share.

Thanks again!!!

GSquared SSC Guru Points: 260824 More actions · Answer 3

justforgroups2004 (12/22/2011)
Thank you for reply, GSquared! 🙂
I have two servers 7 and 2005. Never worked with SSIS, but just looked it up, and it appears to be a part of SQL 2005 ("First released with Microsoft SQL Server 2005, SSIS replaced Data Transformation Services" -wiki).
Then, I guess you found me some holiday reading. Will get a book on SSIS, and dive in.
Just to confirm: I need to run Query1 (pull from Table1) for every instance of RecipientNumber in Table2. And then email Excel format of the output to every instance of RecipientEmailAddress in the Table2, matching the attachments by RecipientNumber.
So, you think SSIS will do that?
If you have any other valuable points/steps/etc, please share.
Thanks again!!!

I've got several SSIS packages that do precisely that. Query one table for lists of e-mail addresses to mail to, and criteria about what data subset to send them, loop through that and use the criteria data to output from another table into Excel and then e-mail as per the list in the first query. Quite easy to set up, and I'm not some sort of SSIS guru. Just enough skill in it to keep from stepping on my own feet, but no Fred Astair, if that makes sense.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

justforgroups2004 Old Hand Points: 315 More actions · Answer 4

Thanks you for sticking around.

I added Execute SQL Task to the Control Flow window with the following parameters:

Full result set

OLE DB

MyDatabase (created new connection to the MyDatabase.Table1)

Direct input

Query1

Added User::mail under Result Set.

Added Foreach Loop Container:

ADO object source variable: User::mail

Rows in the first table option.

Three variables:

User::student_code 0

User::student_mail 1

User::student_name 2

Should I add Data Flow Task or Send Mail Task to the Foreach Loop Container?

Thanks again!

GSquared SSC Guru Points: 260824 More actions · Answer 5

You'll need to add both. First the data flow to export to Excel, then the send mail to ship it. You might also need a file system task to archive the Excel file, or delete it to get it out of the way of the next file (for the next email).

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

justforgroups2004 Old Hand Points: 315 More actions · Answer 6

Finally made this work! And used for a few more reports since then.

Thank you very much for all your help, GSquared 🙂