In an OLE DB Source in an SSIS package, we are having difficulties using multiple parameters in a SQL statement.
Using a single '?' works fine, but I've read that when you want to map more than 1 parameter you should use 'Parameter0, Parameter1, etc'.
The problem is that when we use Parameter0 and Parameter1 and then try to map it, it says that the query contains no parameters.
Can anyone help with the correct way to use multiple parameters in a SQL query that's part of an OLE DB Source task?
Thanks,
Mike
You still use multiple question marks (?) as parameter markers in the query.
For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...|||
I used to have a lot of problems with that when the query was to big or complex (Sub queries, etc). To solve it, and I use it as a standard, I place the query inside of a variable and then use an expression to make it parameterized.
Here is how:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=756434&SiteID=1
|||
 Phil Brammer wrote: |
|
| You still use multiple question marks (?) as parameter markers in the query. For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...
|
I disagree with that. Paramter0, Parameter1..etc is actually the default parameter name used by the OLE DB Source component; so yes you can use it.
|||
| Rafael Salas wrote: |
|
| Phil Brammer wrote: | | | You still use multiple question marks (?) as parameter markers in the query. For OLE DB, your parameter names should be "0", "1", "2", etc... Not "Parameter0", "Parameter1", etc...
|
|
I disagree with that. Paramter0, Parameter1..etc is actually the default parameter name used by the OLE DB Source component; so yes you can use it.
Kirk Haselden comments otherwise in his SSIS book... (Page 182) Granted, that's for Execute SQL Tasks.
Now I guess I'll have to go test it myself as that's what I've always used.|||Either will work. "Parameter0" or just "0".
The issue is likely as Rafael pointed out if you are using a complex query.|||
Neither Parameter0 or just 0 is working
This is the SQL in an OLE DB Source.
At one point, ?0 and ?1 caused the parameter mapping dialog to appear, but these others are not working.
Still scratching my head...
Mike
|||
| Mike Batton wrote: |
|
Neither Parameter0 or just 0 is working This is the SQL in an OLE DB Source. At one point, ?0 and ?1 caused the parameter mapping dialog to appear, but these others are not working. Still scratching my head... Mike |
Can you post your query?|||
With the following options, here's what happens when I click on the parameters button:
Select * from TEW_D_DT where dt_key = Parameter0 and cal_yr_id = Parameter1
Message says that the query contains no parameters
Select * from TEW_D_DT where dt_key = ?0 and cal_yr_id = ?1
This one causes the parameters mapping dialog to open and show two parameters. The problem here is that I set them and then say "OK" to the OLE DB Source Editor and I get this:
Must delcare the scalar variable "@.P10"
Select * from TEW_D_DT where dt_key = ?
This works, but it's only one parameter.
|||
| Mike Batton wrote: |
|
With the following options, here's what happens when I click on the parameters button: Select * from TEW_D_DT where dt_key = Parameter0 and cal_yr_id = Parameter1 Message says that the query contains no parameters Select * from TEW_D_DT where dt_key = ?0 and cal_yr_id = ?1 This one causes the parameters mapping dialog to open and show two parameters. The problem here is that I set them and then say "OK" to the OLE DB Source Editor and I get this: Must delcare the scalar variable "@.P10" Select * from TEW_D_DT where dt_key = ? This works, but it's only one parameter. |
Use question marks - always, and don't number them.
select * from tew_d_dt where dt_key = ? and cal_yr_id = ?
Then, in the parameter mappings, you can use "Parameter0" or "0" for the parameter name to map to your variable.|||
Cool, that worked.
Shouldn't I also be able to use the ? in the select part of the statement?
Mike
|||
| Mike Batton wrote: |
|
Cool, that worked. Shouldn't I also be able to use the ? in the select part of the statement? Mike |
You mean to dynamically select columns? That would not be a good idea as it will change metadata.|||
What about in the case part of a select statement... should that work or not?
Below is part of the actual query that we're trying to get to work.
Mike
select 'ent' AS level_desc,
sku.new_concept_cd AS code_1,
sku.color AS color_name,
case when sls.date_key = ?
then
sum(isnull(sls.total_quantity,0) -
(isnull(sls.store_return_total_quantity,0) +
isnull(sls.catalog_return_total_quantity,0) +
isnull(sls.concierge_return_total_quantity,0))) else 0 end as sales_lw,
sum(isnull(sls.total_quantity,0) -
(isnull(sls.store_return_total_quantity,0) +
isnull(sls.catalog_return_total_quantity,0) +
isnull(sls.concierge_return_total_quantity,0))) as sales_life,
from edw.jjwhse.tew_l_stock_status_sku sku
left outer join
edw_jj.sales.vew_r_sls_sku_loc_wk sls
on sls.sku_key = sku.sku_key
where sls.date_key <= ?
and sku.sku_type_desc = 'sales'
and sls.loc_key not in (6,9,10,11,12,13,14,106)
and sku.new_concept_cd is not null
group by sku.new_concept_cd, sku.color, sls.date_key
|||
| Mike Batton wrote: |
|
| What about in the case part of a select statement... should that work or not? |
Try it... I don't know. If it doesn't work, then as Rafael stated, you can build your query first as an expression in a variable. Build it there first, then use that variable in the OLE DB connector.|||
Anything that changes the structure of the dataflow pipeline will not work; this is in the select part of the query: adding/removing columns; changing data types; changing column names, etc.
Perhaps if you provide an example of what you need to accomplish; somebody around here could help you.
), by the end of the hour we'd learnt more about what it was - i.e. problems with expressions. I just forgot to change the thread title that's all.
ata Conversion-->