Looping through part of a DTS Package?
-
Howzit!
Does anyone have any suggestions about how to loop through part of a DTS Package, breaking the loop once the required reports have been produced and mailed?
I have the following steps in the package:
1. Exec Proc
2. Clear Excel File (.bat)
3. Copy Excel Template (.bat)
4. Exec Proc
5. Data pump to Excel File
6. Mail excel file
I need to loop through steps 2 - 6 until all the reports have been generated and mailed ( the number of reports will vary on a daily basis).
Any ideas, hints, etc, will be greatly appreciated!
gex -
Why not produce All the reports needed first and THEN mail out the reports to the proper recipients? No looping required ....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
All 563 of them?
That is a rather large package....
gex -
Why not have a MAIN stored-procedure that calls the SP's that BCP thge data out to the 563 various Excel files as 1 step and then the next step could be a SP that sends the mail?
Still think there is a NON loop way of skinning this cat that would be a better solution.
At previous company we had 1 DTS package that had 1 SP that called the sub SP's to create daily reports and THEN a 2nd that would e-mail them out. This was the same functionality that was used for month end reports as well. ONLY loop was inside the SP itself to walk the CUSTOMER table to ensure that reports were created by customer and that each customer recipients received the reports together....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
Since you are already shelling out to the command prompt twice during every loop of your package iteration... Maybe you could just code all your logic in a simple Visual Basic application that can be called via the job scheduler?
Sure, DTS is a wonderful tool for doing a lot of different tasks; But always try to keep a perspective that is is not always the best tool for every task at hand.
IMHO, HTH...
-Mike Gercevich
-
I've was having the same type of problem, until someone at work showed me how to do this. Using a package variable that holds the amont of files you wish to process, and a variable to hold the current file your processing. All you need to do is to increment the current file variable everytime you modify a file. You also need to have a start package and end package to check you current file variable and use package steps to reference the start of the loop.
If DTSGlobalVariables("currentFile").Value < DTSGlobalVariables("amountOfFiles").Value Then
' Get reference to the Package
Set oPkg = DTSGlobalVariables.Parent
' reference the step at the start of the loop
Set oStep = oPkg.Steps("DTSStep_DTSActiveScriptTask_2")
' Set the status of that step back to waiting
oStep.ExecutionStatus = DTSStepExecStat_Waiting
Hope this helps
-
Check out how they do the looping in the following example:
Looping, Importing and Archiving
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply