Hi,

I've a testpackage which i'm trying to adjust. What i would like is to
import multiple textfiles (because there are files with the same name) and
archive them.

The package is from sqldts.com so i'll paste the activex scripts:

Script 1 - Defining the Global Variables:

Option Explicit

Function Main()

dim fso
dim fold
dim pkg
dim stpContinuePkg
dim stpExitbadDirectory

' First thing we need to do is to check if our directories are valid.

SET pkg = DTSGlobalVariables.Parent

SET stpContinuePkg = pkg.Steps("DTSStep_DTSActiveScriptTask_3")
SET stpExitBadDirectory = pkg.Steps("DTSStep_DTSActiveScriptTask_2")

DTSGlobalVariables("gv_FileCheckErrors").Value = ""

'We use the FileSystemObject to do our
'Folder manipulation

set fso = CREATEOBJECT("Scripting.FileSystemObject")


'Here we check to make sure the Source folder for the files exists

if fso.FolderExists(DTSGlobalVariables("gv_FileLocati on").Value) <> "True"
then
DTSGlobalVariables("gv_FileCheckErrors").Value =
CSTR(DTSGlobalVariables("gv_FileCheckErrors").Valu e) &_
" " & "Source File directory Not Found"
end if

'Here we check to make sure the Archive folder for the files exists

if fso.FolderExists(DTSGlobalVariables("gv_ArchiveLoc ation").Value) <>
"True" then
DTSGlobalVariables("gv_FileCheckErrors").Value =
CSTR(DTSGlobalVariables("gv_FileCheckErrors").Valu e) &_
" " & "Archive File directory Not Found"
end if

'We predefined the GlobalVariable gv_FileCheckErrors = "" which
'has a length of 2 so we check to see if it has expanded. If it has then
we
'know we had an error and we disable the step that would
'allow us to continue in the package and enable the step
'that takes us out and handles the errors we encountered

If len(DTSGlobalVariables("gv_FileCheckErrors").Value ) > 2 Then
stpContinuePkg.DisableStep = True
stpExitBadDirectory.DisableStep = False
Else
stpContinuePkg.DisableStep = False
stpExitBadDirectory.DisableStep = True

end if

Main = DTSTaskExecResult_Success
End Function
----------------------------------------------------------------------------
------
Script 2 - Bad directories

Option Explicit

Function Main()

Msgbox "You had a Bad Direcory or two please consult: " &_
DTSGlobalVariables("gv_FileCheckErrors").Value

Main = DTSTaskExecResult_Success
End Function
----------------------------------------------------------------------------
------
Script 3 - Looping

Option Explicit

Function Main()

dim pkg
dim conTextFile
dim stpEnterLoop
dim stpFinished

set pkg = DTSGlobalVariables.Parent
set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1")
set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")
set conTextFile = pkg.Connections("Text File (Source)")

' We want to continue with the loop only of there are more
' than 1 text file 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
conTextFile.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 fold
dim pkg
dim counter


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

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

counter = fold.files.count

'So long as there is more than 1 file carry on

if counter >= 1 then

for each fil in fold.Files
DTSGlobalVariables("gv_FileFullName").Value = fil.path
ShouldILoop = CBool(True)
Next

else
ShouldILoop = CBool(False)
End if

End Function

----------------------------------------------------------------------------
------
Script 4 - Loop Around


Option Explicit

Function Main()

dim pkg
dim stpbegin
dim fso
dim fil
dim fold

set pkg = DTSGlobalVariables.Parent
set stpbegin = pkg.Steps("DTSStep_DTSActiveScriptTask_3")

set fso = CREATEOBJECT("Scripting.FileSystemObject")

'The trick to looping in DTS is to set the step at the start of the loop to
an execution status of waiting

stpbegin.ExecutionStatus = DTSStepExecStat_Waiting

'This is how we do our archiving. We use the FileSystemObject to move
'the file to another directory
'I extend this even further in my packages and zip the files up as well.
'I do this using the command line zipping tool from PKWare

fso.MoveFile DTSGlobalVariables("gv_FileFullName").Value
,DTSGlobalVariables("gv_ArchiveLocation").Value

Main = DTSTaskExecResult_Success
End Function
----------------------------------------------------------------------------
------
Script 5 - Finishing

Option Explicit

Function Main()

MSGBOX "Package has Completed."

Main = DTSTaskExecResult_Success
End Function

I really need some help with this.

So as you lop through then why you archive them can you not prepend the name of the "Found In" directory to the nme of the archived
file?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


"Jason" <jasonlewis (AT) hotrmail (DOT) com> wrote

Hi Allen,

I supposed i could, but i don't know how. Can you help me?

J
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Of course.

Have a look here

http://msdn.microsoft.com/library/de...stemobject.asp

When you move the file you can prepend thus renaming the file with whatever you want. In your code you will know the directory in
which you sit as you will no doubt pass it as a parameter to the method that iterated the files in the directory.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


"Jason" <jlewis (AT) homail (DOT) com> wrote