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

• Terry-239890

  Ten Centuries

  Points: 1387

  More actions

  February 20, 2007 at 10:20 pm

  #690808

  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)

     Next

   else

    ShouldILoop = CBool(False)

   End if

  End Function