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 |
|
krishnan
Starting Member
3 Posts |
 Posted - 2002-05-13 : 12:13:38
|
| Hi,Iam looping through the file in folder and changing the datasource dynamically.Iam using the file system object. The problem is if i see the handle count in task manager it careateone handle for every file in the folder. And hence as the no of files are processed my package execution is going slower.Is there any way I can create the object gloablly as i do with the variable. The folllowing is the code to get the next file..Option ExplicitFunction Main() ' Declare FSO Related Variables Dim sFolder Dim fso Dim fsoFolder Dim fsoFilesCollection Dim fsoFile Dim sFileName 'Declare PKG Variables Dim oPKG Dim oConnection Dim lTextStream ' Get Package Object Set oPKG = DTSGlobalVariables.Parent ' Get Source Connection Object Set oConnection = oPKG.Connections("TextFileConnection") ' Import Folder read from global variable sFolder = DTSGlobalVariables("ImportFolder") 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 bFound = True ElseIf bFound = True Then ' New file found oConnection.DataSource = sFileName Set lTextStream = fso.OpenTextFile(fsoFile.Path) DTSGlobalVariables("FileDump") = lTextStream.ReadAll lTextStream.close DTSGlobalVariables("FILENAME") = fsoFile.Name ' Set Pump Step to waiting oPKG.Steps("DTSStep_DTSDataPumpTask_1") _ .ExecutionStatus = DTSStepExecStat_Waiting Exit For End If Nextset fso=nothingSet fsoFolder=NothingSet fsoFile = Nothingmsgbox fsofile.nameSet oPKG=NothingSet oConnection=Nothing Main = DTSTaskExecResult_SuccessEnd FunctionThanks,Sajai. |
|
|
|
|
|
DTS: Looping through files in folder..