Moving data from multiple csv files

Question

Moving data from multiple csv files

pinky

SSCommitted

Points: 1997
More actions
February 16, 2007 at 9:26 am

#69595

Hi,
I have a DTS package created and i am struck at one point. Can anyone help me out with any solution?

I will be getting csv files from different places with different file names (lets say 10 - 15). My package looks somewhat like this. My question is if any one of the csv files is not present in the FTP location this package fails att hat step and would fail the whole package. What I want to do here is if one of the files is missing I would want to go to the next csv file. Is there any actice x script that can do this trick. Please let me know.

CSV FILE
        Data transferred from CSV file
        Onto a sql server table
SQL Server
      Validate the data and
        move the errored records
        onto excel
Excel
        Send an excel with the errored records
        in an email to the
        Concerned person
Email
        An activex script which would
        Delete the current excel and places
       a template file
Active x Script
        On success runs the next csv file
        and the same process continues
CSV FILE
February 19, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Terry-239890 Ten Centuries Points: 1387 More actions · Answer 1

I am attaching one of my sample ActiveX Scripts. Basically it loop through a folder to find files using the Function ShouldILoop. if a file found the function returns TRUE and no files found the function returns False. As I do not exactly know what you require, I cannot comment further. Please read my script then forward your idea.

Function Main()

dim pkg

dim conTextFile

dim stpEnterLoop

dim stpFinished

set pkg = DTSGlobalVariables.Parent

set stpEnterLoop = pkg.Steps("DTSStep_DTSExecuteSQLTask_3")

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

dim sDomain

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

DTSGlobalVariables("gv_FileNameOnly").Value = fil.name

ShouldILoop = CBool(True)

ShouldILoop = CBool(False)

End if

End Function