I've got a DTS package that creates several excel spreadsheets, and
stores them with their last name and date. I use a global variable for
distinct HR names from an Execute SQL task output parameter.

The package loops thru each record in the rs and skips the data pump
if there aren't any records found, instead copying an empty template.
This is good.

The problem is, when rs.EOF is reached, I need it to skip to the end
of the package but it goes thru the data pump and overwrites the empty
template. This is bad. I want it to jump to activex task_7 for
archiving but it won't, see code.

Another approach (Take TWO): I've also created an activex script task
in the Workflow properties of the data pump to test for eof, and when
it gets there it results Main = DTSStepScriptResult_DontExecuteTask
but the problem with this is that the archiving is skipped, and it
looks like there's a failure.
Help, which strategy is better and why won't it step to task_7?
Using SQL Server 2000.
Thanks! Moe


'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()

Dim oRS
Dim objFS
Dim oPkg, stpBegin

Set objFS = CreateObject("Scripting.FileSystemObject")
Set oRS = DTSGlobalVariables("grsThisReport").Value

If oRS.EOF Then

' No Data is found for this HR person, so Pick up template for blank
report and copy it with the appropriate name to the delivery and
archive folders
objFS.CopyFile DTSGlobalVariables("gstrTemplateDir") &
"Caselist_No_Data.xls", DTSGlobalVariables("gstrReportDir").Value &
DTSGlobalVariables("gstrCurrentLName").Value & "Case_" &
DTSGlobalVariables("gstrToday").Value & ".xls"

If DTSGlobalVariables("gintCurrentCount").Value =
DTSGlobalVariables("gintHR_Count").Value + 1 Then
' We're at the EOF, get out of the loop
Set oPkg = DTSGlobalVariables.Parent
Set stpBegin = oPkg.Steps("DTSStep_DTSActiveScriptTask_7")
stpBegin.ExecutionStatus = DTSStepExecStat_Waiting

Else

' Reset the looping Tasks
' Loop back to the starting step of the loop, skipping the whole
data pump thing
Set oPkg = DTSGlobalVariables.Parent
Set stpBegin = oPkg.Steps("DTSStep_DTSActiveScriptTask_2")
stpBegin.ExecutionStatus = DTSStepExecStat_Waiting
End If

End If

' Clear out the global variable.
DTSGlobalVariables("grsThisReport").Value = Null

Set oRS = Nothing

Main = DTSTaskExecResult_Success

End Function

Do you not want something simlar to this article then?

Skip the execution of an individual Task
(http://www.sqldts.com/default.aspx?214)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Moe" <mo (AT) idcomm (DOT) com> wrote