Problem I have is that the query needs to take two parameters.. (Source
and BaseCurrency in the code below) and i can't figure out how to
supply the parameters..
Parameters can be supplied in other task types or transforms .. but can't see how to do it in the Lookup...
PJ
SELECT ForeignCurrency, RateFromFile AS YesterdaysRate
FROM inputrates IR
WHERE fileheaderid in (
SELECT top 1 MAX(ID)
FROM FileInputAttempts FIA
WHERE Source = '?'
AND FIA.BaseCurrency = '?'
AND status = 'SUCCESS'
Group by CAST(FLOOR(CAST(LoadDate AS float))AS datetime)
order by MAX(loaddate) DESC
)Some more info...
When i click on the advanced tab... and select "enable memory
restriction" I can then access the SQL statement by selecting "Modify
the SQL statement" ... then I can also click the "Parameters" button...
But when I DO click Parameters.. I get "Syntax error, permission violation, or other nonspecific error"....
I have checked the query in query analyser, run it to get good results etc...
The Query when it goes into the "Modify SQL statement" box looks like this:
select * from
(SELECT ForeignCurrency, RateFromFile AS YesterdaysRate
FROM inputrates IR
WHERE fileheaderid in (
-- This gives us the last days rates...
SELECT top 1 MAX(ID)
FROM FileInputAttempts FIA
WHERE Source = '?'
AND FIA.BaseCurrency = '?'
AND status = 'SUCCESS'
Group by CAST(FLOOR(CAST(LoadDate AS float))AS datetime)
order by MAX(loaddate) DESC
)) as refTable
where [refTable].[ForeignCurrency] = ?
|||Ahh Haahh!!!
i removed the comments from inside the query and , the parameters box popped up.
but it only contains one parameter...
Looks like its not finding the ?'s at all...
I dunno...|||Took away the apostrphes around the ?'s and got the message:
"Parameter Information cannot be derived from SQL statements with
sub-select queries. Set parameter information before preparing command."
Okay.. so how do I set the parameter information?|||Hi,
To my understanding, Lookup doesn't support dynamic SQL query by using parameters like other transformations can do.
The SQL query you specify will be called only once to fill the cache if you use Full cache mode. That parameter setting SQL is only for Partial cache mode, in this mode, Lookup will call this SQL for each row if not already in cache.
For example, if you have Customer_Name in input stream, you want lookup Customer_ID from Customer table, you either use:
SELECT Customer_Name, Customer_ID FROM Customer
Or:
SELECT * FROM
(SELECT Customer_Name, Customer_ID FROM [dbo].[Customer]) as refTable
WHERE [reftable].[Customer_Name] = ?
(Then set Parameter1 to Customer_Name. If you turn on SQL Profiler, you'll see lots of query like above, only the ? was replaced with real data, e.g. 'Tom', 'Jery', ... After a certain time, when all value have cached, you no longer see this query).
If you want to use runtime value to limit the Lookup cache size, i.e. kind of dynamic SQL, see my workaround blog post here.
Hope this helps.
(p.s. Lots of questions have been asked for Lookup, maybe it's time for someone within SSIS team to re-document it for us, about what it can do, what can't, how it works, etc. )|||
Hmm, thats pretty limited so...
I'll have to try a different approach...
Actually have to say that lookup is , in this case, not very efficient...
I get the idea that the SQL looks like this...
SELECT * FROM
(SELECT Customer_Name, Customer_ID FROM [dbo].[Customer]) as refTable
WHERE [reftable].[Customer_Name] = ?
But the inner SQL there:
SELECT Customer_Name, Customer_ID FROM [dbo].[Customer]
loads every record in a table...
And that's fine if you have a small table.. but I have a huge table from which I want to extract a window of say 20 records... And the only way I have of limiting the records that come from the table is the ability to specify what day (or what currency) these records relate to...
If Lookup insists on loading the whole table into a cache and then selecting from that data... well ... it could be caching a half million records in my case.
That can't be good..
So Lookup seems really limited... only able to perform lookup on small tables.. (well if you want things any way efficient)...
PJ
PJ
|||PJ,
You're absolutely right. There's loads of stuff up on Microsoft Connect regarding the inferiority of the LOOKUP transform. I'd appreciate you going through and voting/adding your comments.
-Jamie
|||
Whoops, how did this end up in its own thread?
I intended it as part of the thread :
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=562631&SiteID=1
Can anyone move it?
PJ
|||[Done!]
|||
Thanks,
I'll have a run over the connect stuff as soon as I get some time...
PJ
|||HI,There is some mis-understanding here.
The SQL query doesn't work in two steps, in fact the whole query will be sent to server and suppose to only fetch one row.
Also caching half-million rows in memory isn't that bad. Say if you only need to lookup an ID from a Name, that's two columns to query. e.g. 10 bytes for Name, 4 bytes for ID, 14 * 500,000 = 7MB, that's even ok for my laptop.
The problem comes when you know that the lookup only involve a subset of the table, then it's meaningless to cache the rest of data.
No comments:
Post a Comment