Post reply

DTS - Multiple Table Import

  • January 15, 2003 at 2:58 pm

    #106128

    Hi

    I have some 40 EXCEL spreadsheets, data of which if have to load into a temp table. I want to create a package that after one file is loaded into the table and the whole process is over, it should go and pick the second file. In this way I don’t have to map the columns every time.

    Would appreciate any help.

  • January 15, 2003 at 4:31 pm

    #446451

    Check out the following,

    http://www.sqldts.com/default.aspx?6,103,246,0,0

    Thanks

    Phill Carter

    --------------------
    Colt 45 - the original point and click interface

  • January 16, 2003 at 11:49 am

    #446547

    Hi Phill

    Thanks for the article. I'm finding it very helpful.But i'm facing difficulty from step 4 to step 5 as i'm using excel files i'm not able to set the precendence and hence keep getting errors.

    Would appreciate if you can help me out with this.

    Thanks

    Pranav

  • January 16, 2003 at 4:34 pm

    #446575

    quote:

    ... i'm facing difficulty from step 4 to step 5 as i'm using excel files i'm not able to set the precendence and hence keep getting errors ...

    Glad to help. What errors are you getting?

    Can you provide more information about the structure of the database and tables that are involved.

    Thanks

    Phill Carter

    --------------------
    Colt 45 - the original point and click interface

  • January 17, 2003 at 7:35 am

    #446621

    Hi Phill

    The error i'm getting is as follows

    "You had a bad directory or two please consult:Source File directory not found archive file directory not found."

    What i did was just copy the scripts from the link that you sent as it is except instead of Text Source i'm using Excel source. According to me the problem is somewhare in defining the location of the file folders, and i dont know how it is done.

  • January 17, 2003 at 2:08 pm

    #446683

    Hi Phill

    This the code which begins the loop and searches for the files in folder(basically step 4 from the link you posted.)My files are located in

    c:\dtsload and the archile files in c:\dtsload\archive.

    Function Main()

    dim pkg

    dim conExcelFile

    dim stpEnterLoop

    dim stpFinished

    set pkg = DTSGlobalVariables.Parent

    set stpEnterLoop = pkg.Steps("DTSStep_DTSDataPumpTask_1")

    set stpFinished = pkg.Steps("DTSStep_DTSActiveScriptTask_5")

    set conExceltFile = pkg.Connections("Excel File (Source)")

    ' We want to continue with the loop only of there are more

    ' than 1 Excel 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

    when i execute this code i get the error

    "Microsoft Data Transformation Service (DTS) Package Connection 'Excel File (Source) Not found"

    would appreciate if you can help in this.

    Thanks

    Pranav

  • January 19, 2003 at 6:12 pm

    #446732

    You need to do two things,

    1) update the global variable gv_FileLocation to point to your directory.

    2) On the line 

    set conExceltFile = pkg.Connections("Excel File (Source)")

    Make sure you have the name of the connection, not it's description.

    What version of SQL Server are you using?

    Thanks

    Phill Carter

    --------------------
    Colt 45 - the original point and click interface

Viewing 7 posts - 1 through 7 (of 7 total)

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