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

You have cut quite a bit from the code on the site but what you have
looks OK. How many files in the directory?

Make sure in the datapump task that you have in "Workflow Properties"
set connection close on completion.

Allan

"chinna jambu via SQLMonster.com" <forum (AT) nospam (DOT) SQLMonster.com> wrote in
message news:forum (AT) nospam (DOT) SQLMonster.com:

I have 3 files in the directory.

All miles are in the network i.e \\cpmqsit\myfolder.

Do i need to set a connection.

thanks

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

You will need to set a connection (Text File Source) to a file of the
same structure as those located in the directory yes. At runtime you
can tell the connection what is the location and name of the file.

"chinna jambu via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote in
message news:forum (AT) SQLMonster (DOT) com:

Do we need to archieve the files?
fso.movefiles dtsglobalvariable("filename").value, dtsglobalvariable
("archive").value.

I don't want to archieve the files. they have to remain in the directory.
but loop has to move through the files and transmit the data in to one table

thanks

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

I only move them because then when new files come into that directory I
know which have been processed and which not.

Allan


"chinna jambu via SQLMonster.com" <forum (AT) SQLMonster (DOT) com> wrote in
message news:forum (AT) SQLMonster (DOT) com: