DTS package and a single global parameter

  • 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!

  • And the DumbAxx award of the week goes to.....Me! the global parameter was typed as a string. Correct. But the delimiters on that string, apparently, are not required.

    Thanks everyone, you've been very helpful.

Viewing 2 posts - 1 through 1 (of 1 total)

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