I've got a SQL2000 DTS that will restore data from a backup (think TodayDatabase and RestoreDatabase).
In that package are 15+ specific Transform Data Tasks with the form:
select * from OneoftheTables where clientid='070066'.
Its incredibly time-consuming and error-prone to manually update each TDT to the desired Clientid.
So, enter the novel idea of parameters:
Then I updated each TDT to:
select * from OneoftheTables where clientid=?
If I click preview for the TDT I get the inimitable error: No value given for one or more required parameters.
A little research encouraged me that this is just a Microslop bug. -- "run the task and it'll be fine..."
Well, not so fast -- yes, the package appears to run fine:
But the results not as desired: Checking the destination DB shows nothing was "restore"/copied:
I tried checking/unchecking Explicit Global Variables (top most capture). That made no difference.
Can anyone shed some light on where the breakdown is on this parameter usage?
PS. Yes, I do realize a script scenario would be much more friendly than maintaining that snakepit of TDTs.
Though, even once I simplify that mess I'll probably still have the same situation. I really am curious and frustrated as to why my efforts at parameterization are failing...
thanks for any help folks!