EXECUTE A DTS TASK FROM ANOTHER TASK

Hi,

The scenario is - I have to transfer n number of excel file data to sql server 2000 and the excel fields are varying. so different activex task are defined. excels results are queried into a global variable using execute sql task. I want to execute this task with different excel file names from a main activex script task.

i tried like this

****************************************

 Dim oPKG
 Dim oConnection
Dim rs
    ' Get Package Object
    Set oPKG = DTSGlobalVariables.Parent
    ' Get Source Connection Object
    Set oConnection = oPKG.Connections("EXCEL CON")
'create recordset
set rs=CreateObject( "adodb.recordset" )




   'Folder read for excel file
    sFolder ="C:\dts excel\"

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsoFolder = fso.GetFolder(sFolder)

    Dim bFound ' Used to exclude files previously exported
    bFound = False

    For Each fsoFile in fsoFolder.Files
        sFileName = sFolder & fsoFile.Name

       ' If oConnection.DataSource = sFileName Then
      msgbox sFileName
            oConnection.DataSource = sFileName
            ' Set Pump Step to waiting
      
oPKG.Steps("DTSStep_DTSExecuteSQLTask_1") _
                  .ExecutionStatus = DTSStepExecStat_Waiting

oPKG.Steps("DTSStep_DTSActiveScriptTask_2") _
          .ExecutionStatus =DTSStepExecStat_Waiting

      set rs=DTSGlobalVariables("xx").Value      
      
      msgbox rs.fields(0)

    '    End If

    Next

      Main = DTSTaskExecResult_Success

****************************************

but what i observed is

oPKG.Steps("DTSStep_DTSExecuteSQLTask_1") _
                  .ExecutionStatus = DTSStepExecStat_Waiting

oPKG.Steps("DTSStep_DTSActiveScriptTask_2") _
          .ExecutionStatus =DTSStepExecStat_Waiting

this lines are not executing at line of execution but after parent task get executed only.
pls give me a solution.


thanks

rajesh

Start Free Trial