asked on

How to create a Package that retrieves records and sends a message

Hello,

I kindly request you to help me on how to create a simple package for the following senior, I'm new in this area and i did a workaround inside Business Intelligence Development Studio, it seems to me it is easy so far but after specifying for me which flows to use.

1-Open a connection to MS SQL Server R2
2-Choose a Table (In my senior it is one table)
3-Write a query

3.1- if the query retrieve records then do the following
Send a Mail
Write a DML like updating another table
Else
do nothing

3.2 This will be scheduled in the database to be run every minute (don't worry the DB Administrator will take care after i send him the package file)

Now:
what flows to use and what about the query i'm using :

Four Main Columns:
Request NO
status ------ Status has values like (Open =1, Closed=2, Canceled=3)
et ---------- End Time
sdate ------ Date

Select Count(*)
From Table
Where status = 1
and
Server Time > et
and
Server Date = sdate

--------------------------
Questions:
1- What is the right type of the server time and date, i'm using datetime2(7) for field End Time and Date for field sdate.

2- After i get a record with a status of Open, i need to change the record to a status of Closed to prevent the job from sending a message every minute, so where shall i write this DML statement:

UPDATE table
SET status = 2
WHERE Server Time > et
and
Server Date = sdate
and
status = 1

3- When sending the message how can i define or pass the request number field of that particular record in the message body.

Thanks..

lcohan

If it is not mandatory for your business process to use a DTSX package to accomplish that task you can sue SQL code like pseudo code below to do that exact same thing easier, and more control on the code side:

declare @report_file_name varchar(30),
            @sql varchar(max),
            @emailbody varchar(4000),
            @subjecttext varchar(1000),
            @parameter sysname

      select @parameter = request_number from your_table_name;

      set @subjecttext = 'This is a TEST email'+@parameter

      set @sql = N'SET NOCOUNT ON
                        SELECT whatever FROM whatever_table WHERE whatever'

      SET @report_file_name = 'file_list_at_' + convert(varchar(10),getdate(), 112) + '.txt'
      SET @emailbody = 'Here is the list you wanted as today(yyyy.mm.dd): '+ convert(varchar(10),getdate(), 102)
      EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'Mail Profile', -- mail profile here
            @recipients='user@mail.com',
            @subject = @subjecttext,
            @body = @emailbody,
            @body_format = 'TEXT',
            @query = @sql,
            @attach_query_result_as_file = 1,
            @execute_query_database = 'YourDB', -- your database name here
            @query_attachment_filename = @report_file_name,
            @query_result_header = 1,
            @query_result_no_padding = 1,
            @query_result_separator = '      ',
            @query_result_width = 1000;

ASKER CERTIFIED SOLUTION

vdr1620

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bduhaish

ASKER

Hello,

I'm testing, so far so good.

bduhaish

ASKER

Thanks to all.

I managed to accomplish my requirements from the valuable links that you provided.

vdr1620

kewl..