troubleshooting Question

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

Avatar of bduhaish
bduhaishFlag for Saudi Arabia asked on
Microsoft SQL Server 2008Microsoft SQL Server.NET Programming
5 Comments1 Solution436 ViewsLast Modified:
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..
ASKER CERTIFIED SOLUTION
vdr1620

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros