Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-01-18 : 18:12:17
|
Victor writes "Here's the questions or problem. I have a DTS package that I only want to execute when data/records reside in a database table. The table contains transactions that a user creates in another process. Each night I have the package setup to run - Monday thru Friday @ 8:30 pm. The first thing the package does is execute a active script task. This task firsts makes a connection to the database and does a select on the table in question and uses an IF statment to determine if anything is in the result set. IF not EOF and not BOF.....
Here's the actual IF logic code:
Function Main()
Dim objRs
Dim objConn
Dim objConnString
Set objConn = CreateObject("ADODB.Connection")
objConnString = "...;" & _
"Server=...;" & _
"UID=...;" & _
"PWD=...;" & _
"Database=...;" & _
"DSN=;"
objConn.Open objConnString
Set objRs = objConn.Execute ("Select PLN_UPDATE_TRANS_NAME From PLN_UPDATE_TRANS_TABLE")
If not objRs.EOF and not objRs.BOF Then
Main = DTSStepScriptResult_ExecuteTask
ELSE
Main = DTSStepScriptResult_DontExecuteTask
End If
Set objRs = Nothing
Set objConn = Nothing
End Function
When data is present in the table, everything works fine. It's when the table is empty that I have the problem. When nothing is in the table the task is failing. This is not necessarily a bad thing but the DBA gets notified and doesn't like the fact that it failed. So my delima is how do I get this task to just end without a failure when nothing is in the table??? I should be able to handle the execution of the rest of the package.
Is there a better way of handling this other than using an active script task? I'm open to suggestions and appreciate all the help!" |
|
|
|
|
|
Executing a DTS package only when data is present within a database table...