Hey guys,

I've got a DTS package disigned to transfer data from multiple remote
same-structure tables into a local centralized table. The source query is
similar in functionality to:

SELECT * FROM TABLE1 WHERE DATECOL > ? UNION
SELECT * FROM TABLE2 WHERE DATECOL > ? UNION
SELECT * FROM TABLE3 WHERE DATECOL > ? UNION
SELECT * FROM TABLE4 WHERE DATECOL > ? UNION
SELECT * FROM TABLE5 WHERE DATECOL > ?

(Note: Actual query may contain 80+ tables.)

I have a global variable containing a date that I map to the parameters (?)
inside the Transform Data Task. The package runs fine. When I save it and
run it again or try to run it from within EM I get the following error:

"No value given for one or more required parameters"

for the Transform Data Task. When I open the package and examine the
parameters to global variable mapping I see that the first 12 parameters are
mapped correctly, the 13th is blank and the 14th on is mapped incorrectly.
I've created a brand new package and recreated all of the steps and received
the same error. If I fix the mapping and run again it works once then fails
from then on. The global variable is being set via a Dynamic Properties
(SQL Query) Task prior to the Transform Data Task and with a Success
presidence constraint.

Is this a bug? Has anyone seen this behavior before? If so any simple
workaround? I'd prefer not to redesign all of these packages if at all
possible.

Thanks

Jerry

Well the work around for future posters was to create a stored procedure on
the remote system that contained the query and to replace the ?s with the
stored procedure parameter name and then map the global variable to the
single stored procedure parameter.

HTH

Jerry
"Jerry Spivey" <jspivey (AT) vestas-awt (DOT) com> wrote

Hello Jerry,

I think you have found the easiest way around doing this. The only other
way I may have approached this because of some drivers' lack of support fopr
parameters at design time would be to have manually built the statement in
an Active Script task up front of the Transform Data Task.

Allan