DTS: Conditional Load 2

[Earth]

I have a SQLServer 2000 table that is only populated with data when an error occurs. The population of this table occurs in a DTS package that is scheduled to run nightly.

I am in the process of creating another DTS package that will be run as part of this same job. I want this new DTS package to generate a txt file with the rows from this error table, and send an email. I have set up the DTS package to do this (ie. load tbl into txt file, email txt file).

QUESTION: Is it possible to make the generation of this txt file or the sending of this txt file CONDITIONAL on whether the tbl is empty? Ie. if the tbl is empty, I do not want to send the email.

Thanks in advance.

 

[scottsanpedro]

Hi...
Yes it possible...
You need to set up a stored procedure that runs as a job BEFORE you run the DTS package that checks to see if any records are found and then if so an automatic email is send using xp_sendmail.

Here's an example I use which is set up in the master database.

CREATE PROCEDURE EMAILLapseMembers


AS

DECLARE @Surname varChar(10)

IF EXISTS(SELECT * FROM Membership.dbo.tblMemberDetails
INNER JOIN Membership.dbo.tblPaymentData
ON Membership.dbo.tblMemberDetails.MM_MemberNo = Membership.dbo.tblPaymentData.PD_MemberNo
WHERE MM_Status = 'LM1' AND DateDiff(Week,PD_RenewalDate,GetDate()) > 12 AND PD_PaymentStatus = 'Pending'
)
BEGIN
EXEC xp_sendmail
@Recipients = 'Scott Fisher;Rosemary Waters;Sue James',
@Message = 'To Accounts/Administrator

This is an automated Email.

The Member(s) listed below have been lapsed due to exceeding 12 weeks after a payment is due

This is confirmation only. No action is required

',
@dbuse = 'Membership',
@Subject = 'Lapsed Members',
@Query = 'SELECT MM_MemberNo As MemberNo, Left(MM_TradingAs,20) AS TradingName FROM Membership.dbo.tblMemberDetails
INNER JOIN Membership.dbo.tblPaymentData
ON Membership.dbo.tblMemberDetails.MM_MemberNo = Membership.dbo.tblPaymentData.PD_MemberNo
WHERE MM_Status = "LM1" AND DateDiff(Week,PD_RenewalDate,GetDate()) > 12 AND PD_PaymentStatus = "Pending"'

UPDATE Membership.dbo.tblMemberDetails
SET MM_Status = 'LPD'
WHERE MM_MemberNo IN(SELECT MM_MemberNo FROM Membership.dbo.tblMemberDetails
INNER JOIN Membership.dbo.tblPaymentData
ON Membership.dbo.tblMemberDetails.MM_MemberNo = Membership.dbo.tblPaymentData.PD_MemberNo
WHERE MM_Status = 'LM1' AND DateDiff(Week,PD_RenewalDate,GetDate()) > 12 AND PD_PaymentStatus = 'Pending')



END
ELSE
RETURN
GO

So set up a stored proc in the master db as above with the IF EXISTS Clause
set up a job that rund before the DTS package
then run the dts package

hope that helps

Scott

 

[quatermass]

Hi,

An alternative to Scott's suggestion would be to have an activex script within the dts which checks the number of records in the table, and will only continue if there are records in the table.

The script would be :

Function Main()
dim cn 'connection object
dim rs 'recordset object
dim str 'sql string
dim no_recs 'record count

'create objects and connect to sql
set cn = createobject("ADODB.Connection&quot
set rs = createobject("ADODB.Recordset&quot
cn.provider="sqloledb"
cn.open "SQL SERVER NAME", "USER NAME", "USER PASSWORD"

'get the count for the source table
str = "select count(*) from DATABASE..TABLE"
set rs = cn.execute(str)
no_recs = rs.fields(0).value

'close objects and set to nothing
cn.close
set rs=nothing
set cn=nothing


'default to failure
x = DTSTaskExecResult_Failure

'if records exist, continue with success
if no_recs <> 0 then x = DTSTaskExecResult_Success

Main = x
End Function



This will work providing the table exists. An empty table will return a count of 0.


The script should be placed before the generation of the text file and email, with the link to the text file generation as on success.

Hope it helps

Kevin **************************************************************
Rock is Dead (Long Live Paper and Scissors)**************************************************************

 

[Earth]

Thanks!

They both sounded like great suggestions. Quatermass' fit in with my existing structure slightly more, hence I have implemented, and it works great