Conditional Logic in DTS...
-
Hello folks,
I have a scheduled DTS that runs a query, exports the results to a text file, creates an email, attaches the text file to it, and sends ......
Great!.....but........
What if the query does not return a result?
If this is the case then I dont want it to send an email. Or potentially I want it to send a different email saying that the query ran, but there was nothing to report.
How do I do this please?
-
Hi,
To insert conditional logic you need to add an active x script.
Hope this helps
Urvashi
-
Well that kinda helps .... could you give me an example of code that might achieve this please.
-
Tony - so I cheated - so WHAT ?! Here's something from BOL for you: actually BOL is really your one-stop-shop to get you started on most everything so you'll likely find at least pointers in the right direction....see if the example below helps you come up with a script of your own...
"The following ActiveX script creates a connection to the Northwind database and the employee table and counts the number of employee records. If employee records are found in the table, the script displays the number of employees and sends a success flag back to the package. Otherwise, the script sends a failure flag. Those flags can be used to trigger other tasks. For example, the success flag can signal that the table has records and then execute a Bulk Insert task. You can use the failure flag to execute a Send Mail task informing a database administrator (DBA) that a potential problem exists.
dim myConn
dim myRecordset
dim iRowCount
' instantiate the ADO objects
set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset")
' set the connection properties to point to the Northwind database,
' using the Customers table
myConn.Open = "Provider=SQLOLEDB.1;Data Source=(local); _
Initial Catalog=Northwind;user id = 'sa';password=''"
mySQLCmdText = "Select 'rowcount' = Count(*) from Customers"
myRecordset.Open mySQLCmdText, myConn
set Flds = myRecordset.Fields
set iRowCount = Flds("rowcount")
If iRowCount.Value = 0 then
Main = DTSTaskExecResult_Failure
Else
MsgBox "The number of customers is: " & iRowCount.Value
Main = DTSTaskExecResult_Success
End If
**ASCII stupid question, get a stupid ANSI !!!** -
Tony,
Check this site for some additional examples:
http://www.sqldts.com/default.aspx?103
Good Luck,
Darrell
-
I do something like this entirely through a SQL Task..
Just use @@ROWCOUNT, conditional statement, and xp_sendmail.
-- M Kulangara
Mathew J Kulangara
sqladventures.blogspot.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply