Parameters in Function in OLE DB Source

Hi there!

I need to use some parameters in an OLE DB Source like that:

select * from mdm.mdm_pos_hierarchies
where dbo.fkt_get_guelt_von (posh_valid_fr) <= dbo.fkt_get_guelt_von (?)
and dbo.fkt_get_guelt_bis (posh_valid_to) >= dbo.fkt_get_guelt_bis (?)

The function returns a valid datetime. These parameters normaly works fine, but not with use as funtion-paramerters. I get feeble error messages (by trying to map the parameters) like that:

"Falsche Syntax in der N?he von ')'. (Microsoft SQL Native Client)" - what means "Wrong syntax near ')'" and says nothing. If i replace the parameters with the mapped value (i.E. '200601') it works fine.

What is the reason for that? It there any solution or a workaround?

Thanks, Torsten

The OLE DB source may be having problems detecting the parameter in your query. I have the impression that it just understands simple queries. The work around is to place the query in a variable, Set the 'EvaluateAsExpression' property to TRUE, and then use an expre ssion to make the query dynamic.

Then in the OLE DB source, choose the option that says your query is in a variable and select the variable from the dropdown list. This is a very common practice to pass the query into source components

Here you can find more posts on how to do that:

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=query+variable+expression&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||

Thanks!

I've found another way: I execute these functions in an "Execute SQL-Task" an put the results into a variable, what i use in the OLE DB Source. Works fine:

select * from mdm.mdm_pos_hierarchies
where dbo.fkt_get_guelt_von (posh_valid_fr) <= ?
and dbo.fkt_get_guelt_bis (posh_valid_to) >= ?

Parameters in an OLE DB Command

Hello!

Is there a way to use an OLE DB Command transform to execute the following SQL query?

"Select sum(length(" + @.[User::varDBName] + ":informix.cyh_t.u_obj)) from " + @.[User::varDBName] + ":informix.cyh_t, " + @.[User::varDBName] + ":informix.cch_t
where " +
@.[User::varDBName] + ":informix.cch_t.d_obj = {D '" + @.[User::strQueryDate] + "'} and " +
@.[User::varDBName] + ":informix.cch_t.n_objid = " + @.[User::varDBName] + ":informix.cyh_t.n_objid"

I get a DB number from an OLE DB Source, do a lookup to get the DB name and then I want to use the OLE DB command to get a result from the query but I can't seem to get this to work. Am I using the wrong transform?

Thanks.

Not quite sure what you want to do here but issuing a SELECT statement from an OLE DB Command won't do anything. It won't insert records into the pipeline for you. If that is what you want to do then LOOKUP is more suitable.

-Jamie

|||Are you querying the same database for all of your information? Or are you going at multiple databases? You might be better off doing some of this work in the control flow if you have different databases. If they are the same databases, then just issue a compound query.|||

Thank you both for your replies. I think I have a flaw with my flow. I am querying multiple databaes so I'll try reworking the flow.

Thanks again!

Parameters for OLEDB Oracle Source Query

Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Oracle)

I am getting the above error while opening the parameter box at OLEDB source for Oracle using SQL command option at Data Access Mode? Can you any one please help me in this regard and trouble shoot this problem..

You could try using an expression instead of parameters: http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

|||

Jamie,

I followed your suggestion , but still i am not able to set the parameters at parameter window, becoz its does't prompt after click on parameter button. Right now i am working on 9i version..can i update with 10g to work on this ?

|||

If you followed my suggestion then you wouldn't have to set parameters using the parameter window. They can be built into the expression.

-Jamie

|||Yup..its working now..Thank you Jamie.

Parameters for Lookup Transformation Query!

Hi Gurus,

I have a Dataflow Task which has an OLE DB Source calling a SP with parameters (?, ?). Then this OLE DB Source is conencted to a Lookup Transform which also calls a SP but on a different database. I am unable to figure out how to pass parameters in a Look up Transform.

In the 'Use Results of an SQL Query' pane of Lookup Transform:

Code Snippet

EXEC GetMonthlyDataExtract 4, 2007

( I am passing month and year values) this works ok.

But when I chage to

Code Snippet

EXEC GetMonthlyDataExtract ?, ?

It says EXEC not supported. Also I can not figure out how to configure parameters since 'Reference Table' Tab of the Lookup Transform does not have any option where we can attach variables to parameters.

Also I am interested to map parameters to variables not to input columns.

If mention if that is not possible or any other alternative.

Your help will be appreciated.

Thanks,

Paraclete

It's a bit of a workaround, but I think you could put your variables in data columns and then use the Lookup in partially cached mode. Go over to the Advanced tab, check Enable Memory Restriction, Enable Caching, and Modify SQL Statement. Put your parameterized EXEC in the statment and set the parameters based on your variable/columns. Instead of executing your procedure in the PreExecute phase, now it will get executed on the first row. Since you're caching the results, the procedure should never get executed again. So it's effectively the same functionality.

Update:
On second thought, the Lookup workaround described here probably won't work. When the Lookup is in partial cache mode, it doesn't perform its own matching, but relies on the modified query and the database engine to perform the matching. Since the parameters to your procedure are not the columns you want to match on, the Lookup won't return the correct matching row.

You may also consider using the Merge Join instead of Lookup.