Passing DTS variables from one Package to Another

I will do my best at trying to explain this.

I have about 200 different text files that I am importing into the same staging table
So in my first DTS package I build a Queue table with the names of the text files (they are all in the same directory)
along with some other data not relevant to the problem.

I capture the Count of this Queue table and store it as a Global Variable.

So, the first DTS gets the Filename from the Queue, then renames the file and imports into the Raw Table,
then deletes that entry from the Queue and then decrements the Count. This package loops until all
the files in the Queue have been processed.

The last step of the 1st Package calls the 2nd DTS package which puts the Data from the Raw table to
a Staging Table.

All this works perfectly.

My problem comes in the last step of the 2nd package.  I am passing the Count Variable from the
first package to the 2nd package (using an Outer Global Variable, I think that is right).  The last step of the 2nd package
is the final processing script which I don't want to execute until all records in the Queue have been
processed.

So the next to last step in the 2nd DTS is an ActiveX task:

Function Main()
 
Dim oPkg

Set oPkg = DTSGlobalVariables.Parent
      
      IF DTSGlobalVariables("LoopCount").Value > 1 Then
            oPkg.Steps(DTSStep_DTSExecuteSQLTask_2).ExecutionStatus = DTSStepExecStat_Inactive
            Main = DTSTaskExecResult_Success
      Else
            
      End If

      Main = DTSTaskExecResult_Success
End Function

So I only want to process the last Step (DTSStep_DTSExecuteSQLTask_2) of the 2nd package when the Count Variable = 1

The Error I am getting is on the ActiveX Task: Step ordinal '0' (base 1) is out of range.

Any help would be appreciated, let me know if you need more information.
Start Free Trial