Parameter Mapping format string

Hi,

I have used Data Flow component that refers to named query of data source view. It is a OLE DB source.

The SQL Command property of data flow component shows SELECT * FROM Tablename as I defiend in named query. I have modified the query to accept a parameter as SELECT * FROM Tablename WHERE Status = ?

Now I need to pass the package variable to this parameter. How to I pass using ParameterMapping property?. What is correct way of passing the parameter mapping ? I tried with @.[TestNS:tatus] variable. But it throws error The parameter mapping string is not in the correct format.

Thanks in advance

Hello Prabha!

The best way to set parameter mappings on your OLE DB source is by using the "Parameters" button in the edit dialog for the source component. Once you've clicked that, a pop-up will appear with a grid where you can select variables you want to map to parameters of your SQL statement. If you have problems with this approach, feel free to post a follow up and one of us will be sure to help you from there.

Thanks, -David

|||

Hi David,

I agree that the suggested approch will help in configuing parameterized SQL Command. But my scenario is to use named query as source.

I do not get the 'Parameters' button as I have selected named query option in OLEDB source. However, I am allowed to change the SQL command property to have the parameter place holder '?' under Properties window after configuring the OLEDB connection Manager.

There is also one more property called 'ParameterMapping' under custom properties of OLEDB source. I do not find any reference on the format of the parameter mapping. Can you forward any reference on this ?

Thanks,

Prabha

|||

Sorry, Prabha, I sort of missed that you're using a DSV named query.

Officially, Named Queries don't support parameters, but as you found, there are ways to manually change the SQL in your DSV, such as by changing the xml directly. I wouldn't recommend that because other things using these DSVs are likely to break.

That having been said, I was able to convince the OLE DB source to bind in variables to the parameters I added manually to the Named Query's SQL, by setting the ParameterMapping manually. The format is:

"<param-name>","<variable-guid>";...

But, instead of constructing it manually, which seems kind of error prone, try this:

1) In the OLE DB Source editor, copy the SQL for your named query to the clipboard.

2) Switch to "SQL Command" mode for your OLE DB Source.

3) Paste the query from the clipboard to the SQL editor.

4) Click "Parameters...".

5) For each parameter, choose a variable.

6) Close the parameters screen, close the OLE DB source editor.

7) Find the "ParameterMapping" field in the Properties editor, copy that to the clipboard.

8) Re-open the OLE DB source editor, switch back to the Named Query.

9) Close the OLE DB source editor

10) Paste the ParameterMapping value from the clipboard back into the properties window

A lot of steps, but I think it's better than trying to hand-assemble the thing. Fair warning though, this isn't supported, so it could stop working at any time. From what I know, DSVs don't officially support parameters, so the safe bet would be to switch to SQL Command mode for this.

-David

|||

Thank you so much David, The given work around works well.

- Prabha