"Jerry Spivey" <jspivey@vestas-awt.com> wrote in message
news:OX0huTgdGHA.1260@TK2MSFTNGP05.phx.gbl...
> 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@vestas-awt.com> wrote in message
> news:OX0huTgdGHA.1260@TK2MSFTNGP05.phx.gbl...
>> 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
>>