Post reply

Executing a task within a loop

  • March 29, 2006 at 5:29 am

    #67407

    Hi peeps,

    I currently have a ActiveX Script Task(vbscript) that loops through a specific directory and returns all the files in that directory. The Filename is assigned to a global variable everytime it loops. Now everytime a filename is returned it should insert it into the "mytable" table in the database. How do I do this???

    Here is my current code...

    [Code]

    Function Main()

    dim oFileName oFileName = "C:\Program Files\"

    dim oFso

    set oFso = CreateObject("Scripting.FileSystemObject")

    dim oFolder set oFolder = oFso.GetFolder(oFileName)

    dim oFile for each oFile in oFolder.Files

    'Wscript.Echo "File Name: " & oFile.path

    'populate variable

    DTSGlobalVariables("gvFileFullName").Value = oFile.path

    'execute a different task to insert data into a table

    'insert into mytable (FullPathName) values (DTSGlobalVariables("gvFileFullName").Value)

    'here i would like to execute sql task 1

    next

    'wscript.echo "Completed"

    'execute active script task 3

    Main = DTSTaskExecResult_Success

    End Function

    [/Code]

    Should I create a a sql task with syntax:

    [Code]

    insert into mytable (FullPathName) values (?)

    [/Code]

    Thanks in advance guys...

  • March 29, 2006 at 9:39 am

    #629549

    you have to use adodb

  • March 30, 2006 at 6:22 am

    #629802

    Like Kevin suggested... use ADODB to either execute a stored proc (preferred) or update the table directly (not preferred).

    Here's a good Microsoft article to get you going:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconcommandstoredproc.asp

    And unless you need those global variables later in the package, don't use them... you are only wasting resources and can create confusion if someone is trying to debug your package. Just use a local variable for oFile.Path.

  • March 30, 2006 at 7:27 am

    #629836

    Another option is to:

    1.  use an activex script to grab the first file
    2. Assign the file path to a global variable
    3. Use an ExecuteSQL Task to insert the file path into your table (as you mentioned in your initial post)
    4. Archive the file to another directory and set the first activex task to "waiting".

    There is good information on looping within a DTS package here:

    http://www.sqldts.com/default.aspx?246

     

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

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