March 11, 2011 at 2:03 am
Hi. I followed this great article written by Allan Mitchell http://www.sqlis.com/post/Shredding-a-Recordset.aspx
The issue I have is if the query below returns no rows the job running the DTS package returns a failure.
OLE DB Source SQL Command:
DECLARE @TriggerDate smalldatetime
SET @TriggerDate = Getdate()+7;
WITH Test AS
(
SELECT
s.EmailAddress,
po.PurchaseOrderId,
DENSE_RANK() OVER (PARTITION BY poi.DueDate ORDER BY po.PurchaseOrderId) AS DR,
poi.DueDate,
poi.QuantityOutstanding
FROM PurchaseOrderItems AS poi
INNER JOIN PurchaseOrders AS po ON poi.PurchaseOrder = po.PurchaseOrder
INNER JOIN Suppliers AS S ON po.Supplier = s.Supplier
WHERE po.SystemType = 'F' AND s.EmailAddress IS NOT NULL AND s.EmailAddress <> ' ' AND poi.DueDate = dateadd(dd, datediff(dd,0,@TriggerDate),0)AND poi.QuantityOutstanding > 0
)
SELECT DR,PurchaseOrderId,DueDate,EmailAddress
FROM Test
GROUP BY
DR,
PurchaseOrderId,
DueDate,
EmailAddress
Question:Can I amend the job (or package) so if no rows are returned an email is sent to administrator saying 'DTS Package run, no records returned'.
Kind Regards,
Phili.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 11, 2011 at 2:08 am
At what point does it fail? Which component fails, I mean?
Please post the text of the error message.
I think you mean SSIS package, rather than DTS, but please confirm.
Are you using a dataflow at all, or is this all being done at Control Flow level?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 11, 2011 at 3:30 am
Hi Phil, yes SSIS (apologies).
SQL Log:
Date10/03/2011 22:15:00
LogJob History (Purchasing Supplier Email)
Step ID1
ServerSQL1
Job NamePurchasing Supplier Email
Step NameExecute ssis Package
Duration00:00:02
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user: Domain\Account. ...-10 22:15:01.84 Source: Send Mail Task The SendMail task is initiated.: 0% complete End Progress Progress: 2011-03-10 22:15:02.24 Source: Send Mail Task The SendMail task is completed.: 100% complete End Progress Progress: 2011-03-10 22:15:02.24 Source: Send Mail Task The SendMail task is initiated.: 0% complete End Progress Error: 2011-03-10 22:15:02.32 Code: 0xC002F304 Source: Send Mail Task Send Mail Task Description: An error occurred with the following error message: "The parameter 'address' cannot be an empty string. Parameter name: address". End Error Progress: 2011-03-10 22:15:02.32 Source: Send Mail Task The SendMail task is completed.: 100% complete End Progress Warning: 2011-03-10 22:15:02.32 Code: 0x80019002 Source: Foreach Loop Container Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the... The package execution fa... The step failed.
Control Flow:
Data Flow Task
For Each Loop Container
Send Mail Task
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 11, 2011 at 3:34 am
Looks like everything worked OK apart from Send Mail - and that failed only because of a missing addressee?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply