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

Question

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

kfschaefer

SSC Eights!

Points: 838
More actions
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

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

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

Calvin Lawson SSChampion Points: 11030 More actions · Answer 1

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

kfschaefer SSC Eights! Points: 838 More actions · Answer 2

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

Calvin Lawson SSChampion Points: 11030 More actions · Answer 3

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

kfschaefer SSC Eights! Points: 838 More actions · Answer 4

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

Calvin Lawson SSChampion Points: 11030 More actions · Answer 5

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