Best way to run a nightly process that manipulates data and sends email via SQL Server 2005

Question

I have an asp app that runs against SQL Server 2005 and I'd like to run processes that:

• query data,
• manipulate data, and
• send emails containing particular data and analysis

What is the easiest way to do this in SQL Server 2005? Can I do this in an SSIS package? Should I use CLR procedures?

Answer 1

You can do this with a SSIS package or better yet with just TSQL.

Can you give us more details if you need further help/ideas?

Answer 2

Maybe, you could meet this scope using Jobs inside SQL Server 2005 (Standard or Higher) and simply executing Queries Stored Procedures. What data and based on which Parameters you need to query?

And what kind of manipulation you need do to? To send the Results you can use SQL Server Mail.

So you'd be completly independent from your ASP app for this tasks.

Answer 3

As others have stated, you could achieve this via either SQL Server Integration Services or a combination of SQL Server Jobs and Stored Procedures.

Personally, I would suggest you look at the SSIS route, as the existing components tasks that are available will provide a significant portion of the functionality that you require, out of the box, thereby speeding up your development process.

If you wanted to get clever with your reporting and deliver results that look really professional, you could define a SQL Server Reporting Services report and call it from within SSIS.

Answer 4

Are the actions related or are they separate? To clarify, does step 2 rely on step 1 and step 3 on step 2 or are they separate processes?

Answer 5

Depending on how complex they are, just go with T-SQL stored procedures, including using xp_send_dbmail to send the email, and then schedule it using a SQL Agent job.