September 7, 2009 at 6:41 am
Hi
I am trying to use a local temp table in the lookup component in my package, but it gives me the error "Invalid object #SSIS_Parameters".
#SSIS_Parameters is the name of the local temp table that i created at the beginning of the package.
The reason i am trying to do this is because i need to use certain dynamic parameters in my lookup query, but there is no way to use variables. So i am using this workaround of loading the temp table with these parameters and then selecting it in the lookup.
Global temp tables work perfectly fine(i just add an extra # and walah!), but i am stumped it's not the same for local temp tables..even after setting the RetainSameConnection property of the connection manager to "True". I have tried delaying the validation and disabling ExternalMetadataValidation but to no avail.
Please help!
I can't use global temp tables because of the risk that another one of the 100 odd packages would access the same temp table.
September 7, 2009 at 6:50 am
Local temp tables have always been tricky because of context. Do you have two references to the same server? Do you create the table in one and then reference it in another?
You said you can't use variables, why? When you build the temp table why not just return a resultset and pick variables out of that. Or is it that you need to work with a set of rows?
Please elaborate..
CEWII
September 7, 2009 at 7:10 am
Thanks for the reply Elliott.
1. I have 3 connection managers in my package. All refer to the same server.
2. I create the temp table using an Execute SQL task using the conn mgr "xxx_transaction". I use the same conn mgr in the lookup as well.
3. I cannot use variables in the lookup component because SSIS does not have any such option.
4. The lookup query is like this :
DECLARE @ProcessStartTime DateTime
DECLARE @ProcessEndTime DateTime
DECLARE @SurveyName varchar(50)
Select @ProcessStartTime = ProcessStartTime, @ProcessEndTime = ProcessEndTime, @SurveyName = SurveyName
from #SSIS_Trans3NF_Parameters
SELECT R.[response_id],ltrim(rtrim(R.[uid])) as [uid],ltrim(rtrim(R.[storeid])) as [storeid],R.[response_date],R.[Term],R.[VisitDate],R.[Source]
FROM [xxx_transaction].[dbo].[Response] R WITH (NOLOCK)
INNER JOIN [xxx_Reporting].[dbo].Survey S on S.survey_id=R.survey_id
Where S.[name]=@SurveyName
and R.Date_Time between @ProcessStartTime and @ProcessEndTime
5. Also, if i use the same query in another Execute SQL task, it works fine. But when i use it in the lookup in a data flow, it gives the error.
September 8, 2009 at 9:45 am
umer.faizal (9/7/2009)
3. I cannot use variables in the lookup component because SSIS does not have any such option.
Sure it does. On the advanced tab, where I'm sure you modified the SQL statement, the parameters button allows you to use the '?' character in your SQL code and replace that with a variable.
It looks to me from the look of your SQL code that hits your temp table that you are only storing 1 row in your temp table anyway. This should be replaced with package variables. Using temp tables in a SSIS package like this is going to be a maintenance nightmare.
September 8, 2009 at 11:51 am
yes u r right, i am storing only 1 row since i need to use only 3 specific values from my package variables for my lookup query.
As to the '?' in the modify sql in advanced tab, these can be mapped only to input columns(not variables) in the data flow that have been mapped in the "Columns" tab. So that means i cannot use any package variables in it's place.
Or am i missing something?
September 8, 2009 at 11:54 am
Hold on.. Then you really aren't doing a lookup at all, you are just trying to add some variables to the pipeline?
If thats true then use a Execute SQL Task (in Control Flow) to get the variables filled and then use a Derived Column component in the pipeline to add them.
CEWII
September 8, 2009 at 12:00 pm
Yes, it only lets you map to columns. Elliot has the solution. Use the Derived Column task to get the values into your pipeline. There, you can use them however you wish.
September 8, 2009 at 12:23 pm
Hold on.. Then you really aren't doing a lookup at all, you are just trying to add some variables to the pipeline?
No, i am actually trying to add some columns to the pipeline from other reference tables based on a matching(like a sql joining operation) column. I want to use the package variables only to filter out the records in this lookup query( because the data set is too large ).
So i dont want to use these variables in my pipeline but rather i want to use it in the where clause of my lookup query.
September 8, 2009 at 1:34 pm
Ok, I see what your problem is.. You have a big lookup and you can't use ? unless the field is in the pipeline already. Simple, so use a derived column component to add the variables to the pipeline then you can use them. Go to the advanced tab and modify the SQL Statement. I believe that will work. You can ignore those fields later.
CEWII
September 9, 2009 at 5:32 am
Unfortunately even if i add those variables to the pipeline using a derived column component, i can use them in the lookup parameters only if they are used in a mapping in the lookup.
Presently one column ([UID]) is used in the lookup mapping, and this is the only option given in the lookup parameters dropdown.
There are already a couple of columns in the pipeline but none of those are shown in the parameters.
Please help!
September 9, 2009 at 10:02 am
I think there's some quarkiness (new word?) with how the look up task works w/ custom queries. Try this:
1. Add your begin/end dates columns into the pipeline via Derived Column transformation.
2. Inside the Lookup Transformation, map both the begin and end date column to your date column in the same table. It will let you map 2 input columns to the same available lookup column.
3. Then, on the Advanced tab, modify the SQL statement. You'll see that it's created a SQL statement that contains both your input columns mapped with an equality operator to your lookup columns. Modify the SQL statement to be >= begin date and <= end date.
September 9, 2009 at 10:57 am
thanks John
but if i add these new mappings in the lookup(begin and end date to lookup date), my lookup operation wouldn't work as expected...
Because i want to do the lookup based only on one single column i.e. [UID]..no other columns need match
So if i do these mappings i am actually changing the lookup logic, which i unfortunately don't want to do.
September 9, 2009 at 12:13 pm
I have to admit I think you are right.. The lookup transfor doesn't allow this.. But you can trick it..
The advanced tab only controls how things are cached, not how they are queried.
But it looks like you can limit the cached data to a subset which if your input query never goes outside of won't ever requery for other outside values.
From BOL:
http://msdn.microsoft.com/en-us/library/ms189962(SQL.90).aspx
Caching SQL statement
View and edit the SQL statement used to populate the in-memory lookup cache.
http://msdn.microsoft.com/en-us/library/ms141821(SQL.90).aspx
CEWII
September 18, 2009 at 4:30 am
Thanks Elliott..sorry fo the delay.
I have tried to get it to work but it wont budge!
Using a partial cache(with ot without the "modify sql"), the data flow just sits at the lookup component for a very long time as it queries the database for every incoming source record.
Using a full cache fails the lookup when the lookup query returns too much data and overloads the memory(even 2 GB aint enough!)
I have learned the lesson the hard way. I should have done joins in the source SP itself instead of getting lured by this fancy lookup component to add columns to the data flow 🙁
For now, I am creating a table at the beginning of the package execution, using this table in the lookup queries to filter the data, then delete the table at the end. I see no other option...
Thanks for the replies..
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply