importing files using dts

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hi
I am using looping,importing and archieving example for importing multiple
files into sql server database.

here is my code

create table -----> Active x Script 1 -------> Text File Source ------>
transformation task -----> Active x script 2

Active X script 1
*********************

Option Explicit

Function Main()

dim pkg
dim conTextFile
dim stpEnterLoop
dim stpFinished

set pkg = DTSGlobalVariables.Parent
set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1")

set conTextFile = pkg.Connections("Text File (Source)")


if ShouldILoop = True then

stpEnterLoop.DisableStep = false
conTextFile.DataSource = DTSGlobalVariables("Filename").Value
stpEnterLoop.ExecutionStatus = DTSStepExecStat_Waiting


End if

Main = DTSTaskExecResult_Success
End Function

Function ShouldILoop

dim fso
dim fil
dim fold
dim pkg
dim counter


set pkg = DTSGlobalVariables.Parent
set fso = CREATEOBJECT("Scripting.FileSystemObject")

set fold = fso.GetFolder(DTSGlobalVariables("Folder").Value)

counter = fold.files.count



if counter >= 1 then

for each fil in fold.Files
DTSGlobalVariables("Filename").Vmsgbox fil.path
ShouldILoop = CBool(True)


Next


End if

End Function

Active x script 2
***********************
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

Dim pkg
Dim stpBegin
dim fso
dim fil
dim fold
set pkg = DTSGlobalVariables.Parent
msgbox "suchi"
set stdBegin = pkg.Steps("DTSStep_DTSActiveScriptTask_1")
set fso = Createobject("Scripting.FileSystemObject")

stdBegin.ExecutionStatus = DTSStepExecStat_Waiting

Main = DTSTaskExecResult_Success
End Function

the loop is going through the files in the directory, but the last file
data is only loaded into the temp table.
Can any suggest me a solution for my problem.

thanks

--
Message posted via http://www.sqlmonster.com
.

Relevant Pages

  • Re: Execute Process task fails after package is saved
    ... ' Visual Basic ActiveX Script ... Dim sDestinationFile ... 'This is the source file that the DTS Package created ...
    (microsoft.public.sqlserver.dts)
  • import multiple files with subdirectories
    ... Script 1 - Defining the Global Variables: ... dim fold ... SET pkg = DTSGlobalVariables.Parent ...
    (microsoft.public.sqlserver.dts)
  • exec sql task within a loop
    ... I have a package with 3 sql execution tasks and a active x script. ... and in my activex script im trying to loop through the results and ... dim variable ...
    (microsoft.public.sqlserver.dts)
  • Re: importing files using dts
    ... Active X script 1 ... dim conTextFile ...
    (microsoft.public.sqlserver.dts)
  • RE: export user accounts from NT 4.0 domain
    ... ATTENTION THE SCRIPT MUST BE RUNNED FROM A COMPUTER WHERE EXCEL IS ... from the information in a Microsoft Excel spreadsheet. ... Dim strLast, strFirst, strMiddle, strPW, intRow, intCol ... On Error GoTo 0 ...
    (microsoft.public.windows.server.scripting)