Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DTS package and a single global parameter Expand / Collapse
Author
Message
Posted Tuesday, November 05, 2013 12:43 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 3:04 PM
Points: 19, Visits: 61
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!


Post #1511595
Posted Tuesday, November 05, 2013 3:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, November 05, 2013 3:04 PM
Points: 19, Visits: 61
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.
Post #1511653
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse