February 13, 2006 at 1:42 am
I have a package that loops through a set of subdirectories to upload a specific text file from each subdirectory.
The package works fine and the looping code does what it was designed to do.
I now have a situation where I need to apply the looping procedure to 20 files per directory each going to a different table.
My looping code changes the datasource for the input files to the desired directory.
Let us suppose that I have a connection for each of the 20 files and 4 database connections so up to 4 uploads can take place simultaneously.
I am not sure of the best way to make sure that all 20 uploads are complete before the looping code tries to skip to the next directory. To clarify, let us suppose that the 4th file finishes 1st. I don't want the loop to try updating the datasource until all the other files have been successfully uploaded.
February 14, 2006 at 8:01 am
I'd keep track of the files you've already uploaded in a table, and move the processed file to a "processed" directory. It's good practice anyhow. Do an inner and outer loop. I am unsure how you're getting DTS to loop, but I'd be using recursion.
February 14, 2006 at 8:16 am
I've used an ActiveX task to handle the entire workflow before. I would set up a DTS in normal workflow fashion, then disable the workflow steps after the ActiveX task. Then in ActiveX, I would use the DTS object model to Execute the disabled steps:
Dim oPackage
Set oPackage = DTSGlobalVariables.Parent
oPackage.Steps ("[task1 name]").Execute
oPackage.Steps ("[task2 name]").Execute
February 14, 2006 at 8:38 am
That's really interesting, but I think recursion is more fun! unless you run out of memory that is (guilty).
February 14, 2006 at 8:44 am
I like to think of it being the OOP way of doing DTS. Where the objects are the disabled tasks and you are using the main ActiveX task to control whether or not the objects are being called.
February 14, 2006 at 9:54 am
February 14, 2006 at 10:31 am
OK my eventual solution (which probably isn't the best) was to have my begin loop code enable/disable the relevant steps but store the number of steps in a global variable.
At the end of each transformation I had a step to increment a counter and if the counter was less than the global variable it did not continue.
February 15, 2006 at 3:16 pm
My CIO almost hit the floor when I showed him. That is awesome!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply