Thanks for your quick response!!!

I am going to try this!!!!!

Hans Vergouwen

I got it 90% working I Guess!!
The only way I get it working now is to fill in the full path to the
file.. I cannot get it working by referring to the global variable
gv_FileFullName....
see the full script:::

plz help...

Hans..


1.

' 246 (DefineTheGVs)
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_FileLocation").Value) <>
"True" then
DTSGlobalVariables("gv_FileCheckErrors").Value =
CSTR(DTSGlobalVariables("gv_FileCheckErrors").Value) &_
" " & "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_ArchiveLocation").Value)
<> "True" then
DTSGlobalVariables("gv_FileCheckErrors").Value =
CSTR(DTSGlobalVariables("gv_FileCheckErrors").Value) &_
" " & "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



2.

' 246 (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



3.


SCRIPT BEGINLOOP

' 246 (Begin Loop)
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




4. HEADERSCRIPT BEFORE DATAPUMP!


'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************

Function Main()

dim oFSO, oStream, strTextRead, position, myValue
set oFSO = CreateObject("Scripting.FileSystemObject")
set oStream = oFSO.OpenTextFile ("c:\test\05557201.DAT", 1)
strTextRead = oStream.Readline
position = instr(strTextRead, ";") - 1
myValue = left(strTextRead, position)
DTSGlobalVariables("lidnummer").Value = myValue
msgbox myValue
set oStream = Nothing
set oFSO = nothing
Main = DTSTaskExecResult_Success
End Function



5. TEXTFILE SOURCE:
6. SQL SERVER

Column to column mappings except for the header


DTS active x transformation:
For the header transform:


'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************

' Copy each source column to the destination column
Function Main()
Main = DTSTransformStat_OK
DTSDestination("lidnummer") = DTSGlobalVariables("lidnummer").Value
End Function



7.


SCRIPT LOOP AROUND


' 246 (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

8.


SCRIPT FINISHED:
' 246 (Finished)
Option Explicit

Function Main()

MSGBOX "Package has Completed."

Main = DTSTaskExecResult_Success
End Function

..

Yes my import works!!!!!....

The only thing I need to fix is that sometimes the file only contains a
headerrule and no data

when I run the DTS JOB it stops with an error and says that columns
cannot be fount...

The header rule has about six columns and the datarows have 24
columns... does anybody know how to create a check before opening the
file to be imported to determine if the file has datarows in it and
not only a header....

Thanks !
Hans