Halt DTS on 0 rowcount return

[redoakhg]

We have a simple DTS that basically dumps records into a text tab delimited file nightly. We would like to first check to see if there are records prior to creating the file. Something like an ActiveX task that runs the query, gets rowcount and if rowcount = 0 halt the DTS otherwise process the file.

Here's the query to get the records:

select emailaddress as EMAIL from 
view_lead_display2
where (DateDiff (day,LeadInsertDate ,getdate() )=1)  
and unsub='0' and leadid not in (select distinct leadid from dbo.Leads_Reassigned)

Any help with the task would be greatly appreciated.

 

[AlexCuse]

You can set up a global variable in your package (I believe PACKAGE --> PROPERTIES, then click the GLOBAL VARIABLES tab), and then execute this query in an execute sql task

select count(emailaddress) as CNT from 
view_lead_display2
where (DateDiff (day,LeadInsertDate ,getdate() )=1)  
and unsub='0' and leadid not in (select distinct leadid from dbo.Leads_Reassigned)

You can set up an output parameter within your SQL task to assign this query result to your global variable, then key off of your global variable to decide what to do next (think of it like a fork in your package's flow)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[redoakhg]

Alex,
Thanks for the feedback. I understand what you are talking about, and I can visualize what needs to be done. I am having trouble calling the global variable, and am not quite sure if the global variable is being written to. I am pretty weak in VBscript...right now here is my vbscript code

Function Main()
   If cnt = 0 Then Main = DTSTaskExecResult_Failure
   Else Main = DTSTaskExecResult_Success
End Function

From what I can tell, the code seems kosher. But maybe my global variable is not being set properly? If you, or anyone, could provide some more guidance, that would be awesome. Thanks.

 

[AlexCuse]

You can't reference a global variable like you would a regular variable.

Refer to it like this:

DTSGlobalVariables("cnt").Value

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[redoakhg]

Alex,
Ok, got that down. Now I just have one more question. I have no idea why this script will not work. As far as if...else logic goes, I think I am on target but maybe I am not doing it correctly for VB. Here is the code:

Function Main()
If DTSGlobalVariables("cnt").Value = 0 Then Main = DTSTaskExecResult_Failure
Else  Main = DTSTaskExecResult_Success
End Function

And my error is:
Error Description: Error Code: 0
Error Source= Microsoft VBScript compilation error
Error Description: Expected 'End'

Please help!

 

[AlexCuse]

Just one more simple change.

Function Main()
If DTSGlobalVariables("cnt").Value = 0 Then 
Main = DTSTaskExecResult_Failure
Else  
Main = DTSTaskExecResult_Success
[b]End If[/b]
End Function

I have come to like the c-style if syntax much better, is that what you are used to?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[redoakhg]

Alex,
Yea, I am used to c-style, java style if statements (my first language was java). In fact, the only other place where I have seen if statements with that syntax is using PL/SQL. Thanks for the help.

I am still getting an error tho! This is getting to be quite frustrating. Here is my layout, and hopefully you can help me pinpoint the problem, because I am at a loss. Everything seems to be in line.

My DTS package starts off at "check for records" SQL task. Here, I use the query:

select count(emailaddress) as CNT from
view_lead_display2
where (DateDiff (day,LeadInsertDate ,getdate() )=1)  
and unsub='1' and leadid not in (select distinct leadid from dbo.Leads_Reassigned)

Then, I click Parameters->Output Parameters, with Output Parameter Type being Row Value, and Parameter Mapping with Parameters CNT and Output Global Variables cnt. On Success, I go to "should continue" ActiveX Script. In this script, here is the code:

Function Main()
If DTSGlobalVariables("CNT").Value < 1 Then
Main = DTSTaskExecResult_Failure
Else  
Main = DTSTaskExecResult_Success
End If
End Function

From here, if On Failure, go to "do nothing" ActiveX Script which does nothing. If On Success, go to "change file name" ActiveX Script. Here is the code:

Function Main()

mydate =now()
sFilename = "C:\Documents and Settings\Administrator.REDOAKGROUP\My Documents\Yamaha\red_oak_optout_emails_" &  Right(Year(mydate), 4)
If Month(mydate) < 10 Then sFilename = sFilename & "0" & _
Month(mydate) Else sFilename = sFilename & Month(mydate)
If Day(mydate) < 10 Then sFilename = sFilename & _
"0" & (Day(Mydate) - 1) Else If Day(Mydate) = 10 Then sFilename = sFilename & _
"0" & (Day(Mydate) - 1) Else sFilename = sFilename & (Day(mydate) - 1)
sFilename = DTSGlobalVariables("LogFilePath").Value & _
sFilename &  ".txt"
Set oConn = DTSGlobalVariables.Parent.Connections("Connection 2")
oConn.DataSource = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function

This script appends the current date minus 1 to the end of the output file. On Success, this goes to "Connection 1" DB Provider. Then from "Connection 1", I have a Transform Data Task, with the sql code:

select emailaddress as EMAIL from 
view_lead_display2
where (DateDiff (day,LeadInsertDate ,getdate() )=1)  
and unsub='1' and leadid not in (select distinct leadid from dbo.Leads_Reassigned)

This outputs to "Connection 2", a text file destination. Now, I know for a fact that change file name, Connection 1, and Connection 2 work correctly. The error I get is when I try to run "should continue". All I get is this crypt message: "The task reported failure on execution". As I said before, I am completely new to DTS and everything seems to fine to me, as far as workflow goes. Do you see anything that I am doing wrong?

 

[AlexCuse]

Which step is failing?

Ignorance of certain subjects is a great part of wisdom

 

[redoakhg]

It is failing on the "should continue" ActiveX Script, with the error "The task reported failure on execution". The process starts at the "check for records" SQL task, and that executes succesfully.