Post reply

Need Ideas for Passing a variable(filename) then importing file

  • November 17, 2004 at 5:23 pm

    #60443

    I currently have a DTS package that creates a view which contains a list of files that need to be opened one by one and imported into(update another table with the data contained within these files.

    I will need to be able to pass the directory path and the actual file name - import the file and then update the ProjectDemand table with the date, then loop through the list repeating this process until the end of the file.

    What do you recommend as the best approach.  Which DTS tool is the best for theis process - or do you recommend some function -

    My thought was to some how pass the filename as a variable - import the file and then update the necessary table.  I am a little confused on how to do the loop.  Help me Please.

    Thanks,

    Karen

     

  • November 19, 2004 at 6:13 pm

    #530940

    If the schema for each file is different, you're on your own (hope you're good at VB!).  If every file is the same, though, you can modify the package in the link below; it's loops through a directory and imports all the files in it.

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

    Signature is NULL

  • November 22, 2004 at 11:17 am

    #531100

    Thank you for the suggestions - However, my VB skills are limited - I was trying to use DTS to create the global variable.  I understand how to set the variables - What I am still confused on is how to set the value of the variable when it is constantly changing.

    According the the DTS  you have establish the file path(hard coded) - I need to use Lookup of somekind - I have a Temp table that will store the value.

    Also, if I change the code behind a DTS how do a view  it back in DTS?

    Karen

  • November 22, 2004 at 1:46 pm

    #531117

    No, the package does not have a hardcoded path.  You point it at a directory (global variable "gv_FileLocation") and it loops through it and imports all files.

    cl

    PS:  You could also use the dynamic properties task to reset the connection.

    Signature is NULL

  • November 22, 2004 at 1:51 pm

    #531118

    I am confused in DTS when setting variables it ask for a path or directory, how do I get around that?  Also, If I change the code I will no longer be able to use DTS to configure my package - Correct? Could I still access the DTS from within another Package by using an ACTIVEX Item within the new package?

    Kare

  • November 22, 2004 at 2:13 pm

    #531120

    You don't need to use ActiveX to pass a variable from one package to another; you can do this by correlating the inner and outer variables in the "Execute Package" task (see BOL).

    cl

    Signature is NULL

Viewing 6 posts - 1 through 5 (of 5 total)

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