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..
Our community of experts have been thoroughly vetted for their expertise and industry experience.