Hi everyone,

Short introduction; I work for Biretco BV in the netherlands; we are a
retail organisation for bicycle stores!

Now the case is :
Everyday we sell products in each store. (approx 500 shops)
At night the shops upload their file on our FTP..
I collect the files (there are 500shops x 1 file) about 500 files
everyday

All the files have the same format:
HEADERRULE:
00093;Profile Rudie Assink;Raalte;20.07.06;2.0

DATARULE:
20.07.06;08:55;29;0200006543;;2;Rudie;3020.0151;87 12265014549;BAND
PROFILE 3000
BREAK;3020;Profile;3;5.30;15.95;1;13.40;15.95;13.4 0;15.95;0.00;0.00;8.10;60.5

I have already created looping, importing and archiving according to
article:
http://www.sqldts.com/default.aspx?246

with this I mean all files are imported in my SQL table!

The problem is that I cannot identify the rules in the SQL table ....
what row belongs to which bicycle shop????

To achieve this the header row's first column; (in my example : 00093)
needs to be imported in every row in the sql server before starting
with the next file!!

Because I don't understand VB i cannot write my own activex
transformation...

I think it can be done by storing the first column in a variable... but
how?

thanks in advance!!!

Hans Vergouwen

Hi Hans,

Within your loop, before you import the data, read the second line of the
text file and extract the ShopID number, e.g. 00093. (see below how to do
it). Save it in a global variable, then use it within a Data Transformation
ActiveX script. i.e. you have to create a new ActiveX mapping to the ShopID
destination column in your database, and use a line like this:

DTSDestination("ShopID") = DTSGlobalVariables("ShopID").Value

The other column mappings can stay as Copy Column mappings.

To obtain the ShopID from your file, here is a very simple ActiveX script
which assumes the ShopID is always on the second line before the first
semicolon. The script writes the ShopID to a global variable called ShopID
that you need to create in the package. This script must come before the data
pump task:

dim oFSO, oStream, strTextRead, position, myValue
set oFSO = CreateObject("Scripting.FileSystemObject")
set oStream = oFSO.OpenTextFile("c:\MyDataFile.txt", 1)
strTextRead = oStream.Readline
strTextRead = oStream.Readline
position = instr(strTextRead, ";") - 1
myValue = left(strTextRead, position)
DTSGlobalVarialbes("ShopID").Value = myValue
msgbox myValue
set oStream = Nothing
set oFSO = nothing

Note that in your case the file name will change for each loop iteration.
But I am assuming you have figured out how to do that part because you say
you have implemented a loop already. Use the global variable that contains
your file name in place of the hard-coded "c:\MyDataFile.txt" that I have put
here.

This should be very easy to implement.

Hope this helps.


Charles Kangai, MCT, MCDBA





"Hans Vergouwen" wrote:

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_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



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