Results 1 to 1 of 1
  1. #1
    Join Date
    Jul 2005
    Location
    New Zealand
    Posts
    61

    Unanswered: Looping to load files in DTS

    Hello,

    I am trying to implement looping in DTS to load the contents of 3 .txt files into 3 tables in a SQL2K database. The package I've designed is adapted from one I found on sqldts.com and is shown in the attached file.

    I am dynamically setting the connection DataSource using a global variable to retrieve the name of the file to be loaded. My 'Begin loop' ActiveX script is:
    Code:
    '**********************************************************************
    '  Visual Basic ActiveX Script
    '************************************************************************
    ' Loop through all input files and determine type of file
    Option Explicit
    
    Dim stpEnterLoop
    
    Function Main()
    
    	Dim pkg
    	Dim conInputFile 
    	Dim stpFinished
    
    	set pkg = DTSGlobalVariables.Parent
    	set stpEnterLoop = pkg.Steps("Copy Lassy Raw Data to LassyInitialPayment table Step")
    	set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")
    	set conInputFile = pkg.Connections("Input Files")
    
    	' We want to continue with the loop only if there is
    	' one or more files in the directory. If the function ShouldILoop
    	' returns true then we disable the step that takes us out of the package
    	' and continue processing
    
    	if ShouldILoop = True then
    		stpEnterLoop.DisableStep = False
    		stpFinished.DisableStep = True
    		conInputFile.DataSource = DTSGlobalVariables("gv_FileFullName").Value
    		stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting
    	else
    		stpEnterLoop.DisableStep =True
    		stpFinished.DisableStep = False
    		stpFinished.ExecutionStatus = DTSStepExecStat_Waiting
    	End if
    
    	Main = DTSTaskExecResult_Success
    End Function
    
    
    Function ShouldILoop
    
    	Dim fso
    	Dim fil	
    	Dim LassyCFLDfolder
    	Dim LassyIPfolder
    	Dim Trintecfolder
    	Dim pkg
    	Dim LassyCFLDcounter
    	Dim LassyIPcounter
    	Dim Trinteccounter
    
    	
    	set pkg = DTSGlobalVariables.Parent
    	set fso = CREATEOBJECT("Scripting.FileSystemObject")
    	
    	set LassyCFLDFolder = fso.GetFolder(DTSGlobalVariables("gv_LassyCFLDFileLocation").Value)
    	set LassyIPfolder = fso.GetFolder(DTSGlobalVariables("gv_LassyIPFileLocation").Value)
    	set Trintecfolder = fso.GetFolder(DTSGlobalVariables("gv_TrintecFileLocation").Value)
    
    	LassyCFLDcounter = LassyCFLDfolder.files.count
    	LassyIPcounter = LassyIPfolder.files.count
    	Trinteccounter = Trintecfolder.files.count
    
    	' So long as there are files in either directory carry on
    	' Lassy files will be loaded first then Trintec files
    
    	if  LassyCFLDcounter >= 1 then
    
    	for each fil in LassyCFLDfolder.Files
    		set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_2")
    		DTSGlobalVariables("gv_FileFullName").Value = fil.path
    		ShouldILoop = CBool(True)
    	Next
    
    	ElseIf	LassyIPcounter >= 1 then
    
    	for each fil in LassyIPfolder.Files
    		set stpEnterLoop = pkg.Steps("Copy Lassy Raw Data to LassyInitialPayment table Step")
    		DTSGlobalVariables("gv_FileFullName").Value = fil.path
    		ShouldILoop = CBool(True)
    	Next
    
    	ElseIf	TrintecCounter >= 1 then
    		
    		for each fil in Trintecfolder.Files
    			set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1")
    			DTSGlobalVariables("gv_FileFullName").Value = fil.path
    			ShouldILoop = CBool(True)
    		Next
    	else
    		ShouldILoop = CBool(False)
    	End if
    
    End Function
    So basically, as long as there is at least one file of any of the 3 types to load, keep going around the loop.

    The data from the files is loaded via 3 DataPump tasks and I use pattern matching on the filename in a WorkFlow properties ActiveX script to determine which DataPump task to run, e.g.
    Code:
    '**********************************************************************
    '  Visual Basic ActiveX Script
    '************************************************************************
    'if the input file is a Lassy raw data file, execute the task
    
    Function Main()
    	set typeoffile = new regexp
    	typeoffile.ignorecase = true
    	typeoffile.pattern = "raw\b"
    	IF typeoffile.test(DTSGlobalVariables("gv_FileFullName").Value) then
    		Main = DTSStepScriptResult_ExecuteTask
    	ELSE
    		Main = DTSStepScriptResult_DontExecuteTask
    	END IF
    End Function
    I then want to go back around the loop to load the next file using the 'Loop around' task. Obviously it is not possible for all 3 DataPump tasks to have succeeded (or completed) so I have tried to get around the precedence problem by using another ActiveX script in the WorkFlow properties of the 'Loop around' task:
    Code:
    '**********************************************************************
    '  Visual Basic ActiveX Script
    '************************************************************************
    
    ' If any of the file loading DataPump tasks succeeded, excute the task
    Option Explicit
    
    Const DTSStep_Trintec = "DTSStep_DTSDataPumpTask_1"
    Const DTSStep_LassyCFLD = "DTSStep_DTSDataPumpTask_2"
    Const DTSStep_LassyIP = "Copy Lassy Raw Data to LassyInitialPayment table Step"
    
    
    Function Main()
    
    	Dim oPkg, oConstraints, oConstraint
    
    	' Get reference to the package
    	Set oPkg = DTSGlobalVariables.Parent
    	' Get reference to the PrecedenceConstraints collection
    	Set oConstraints = oPkg.Steps("DTSStep_DTSActiveScriptTask_4").PrecedenceConstraints
    
    	If ("DTSStep_Trintec").ExecutionResult = DTSStepExecResult_Success Then
    		' Set the precedence constraint for DTSStep_Trintec to the execution result (success)
    		Set oConstraint = GetConstraint(oConstraints, DTSStep_Trintec)
    		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
    		oConstraint.Value = DTSStepExecResult_Success
    
    		'Set the precedence constraint for DTSStep_LassyCFLD and DTSStep_LassyIP
    		' to the execution status (inactive or waiting)
    		' This allows the overall Workflow precedence to succeed
    
    		Set oConstraint = GetConstraint(oConstraints, DTSStep_LassyCFLD)
    		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus
    		oConstraint.Value = DTSStepExecStat_Inactive Or DTSStepExecStat_Waiting
    		oPkg.Steps(DTSStep_LassyCFLD).ExecutionStatus = _
    			DTSStepExecStat_Inactive
    		Set oConstraint = GetConstraint(oConstraints, DTSStep_LassyIP)
    		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus
    		oConstraint.Value = DTSStepExecStat_Inactive Or DTSStepExecStat_Waiting
    		oPkg.Steps(DTSStep_LassyIP).ExecutionStatus = _
    			DTSStepExecStat_Inactive
    		
    		Main = DTSStepScriptResult_ExecuteTask
    
    	ElseIf("DTSStep_LassyCFLD").ExecutionResult = DTSStepExecResult_Success Then
    		Set oConstraint = GetConstraint(oConstraints, DTSStep_LassyCFLD)
    		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
    		oConstraint.Value = DTSStepExecResult_Success
    
    		Set oConstraint = GetConstraint(oConstraints, DTSStep_Trintec)
    		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus
    		oConstraint.Value = DTSStepExecStat_Inactive Or DTSStepExecStat_Waiting
    		oPkg.Steps(DTSStep_Trintec).ExecutionStatus = _
    			DTSStepExecStat_Inactive
    		Set oConstraint = GetConstraint(oConstraints, DTSStep_LassyIP)
    		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus
    		oConstraint.Value = DTSStepExecStat_Inactive Or DTSStepExecStat_Waiting
    		oPkg.Steps(DTSStep_LassyIP).ExecutionStatus = _
    			DTSStepExecStat_Inactive
    		
    		Main = DTSStepScriptResult_ExecuteTask
    		
    	ElseIf("DTSStep_LassyIP").ExecutionResult = DTSStepExecResult_Success Then
    		Set oConstraint = GetConstraint(oConstraints, DTSStep_LassyIP)
    		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
    		oConstraint.Value = DTSStepExecResult_Success
    
    		Set oConstraint = GetConstraint(oConstraints, DTSStep_Trintec)
    		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus
    		oConstraint.Value = DTSStepExecStat_Inactive Or DTSStepExecStat_Waiting
    		oPkg.Steps(DTSStep_Trintec).ExecutionStatus = _
    			DTSStepExecStat_Inactive
    		Set oConstraint = GetConstraint(oConstraints, DTSStep_LassyCLFD)
    		oConstraint.PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus
    		oConstraint.Value = DTSStepExecStat_Inactive Or DTSStepExecStat_Waiting
    		oPkg.Steps(DTSStep_LassyIP).ExecutionStatus = _
    			DTSStepExecStat_Inactive
    
    		Main = DTSStepScriptResult_ExecuteTask
    	Else
    		MsgBox "Setting status failed"
    		Main = DTSStepScriptResult_DontExecuteTask
    	End If
    
    	Set oPkg = Nothing
    	Set oConstraints = Nothing
    	Set oConstraint = Nothing
    
    End Function
    
    Function GetConstraint(oConstraints, sStepName)
    	' Get Constraint by Source Step Name
    	Dim oConstraint
    	For Each oConstraint In oConstraints
    		If oConstraint.StepName = sStepName Then
    			Set GetConstraint = oConstraint
    			Exit For
    		End If
    	Next
    End Function
    However, this is not working so what do I need to do to get the 'Loop around' task to run? Would I be better off using 3 separate packages to load each file?

    Thanks very much
    Lempster
    Attached Thumbnails Attached Thumbnails Looping.JPG  

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •