Lookup Transformation: How can I join tables in different databases

I want to join tables that reside in different databases (same instance). The Lookup object only lets me select from one data source. Is there anyway to lookup using more than one data source? I can write a SQL query to lookup across databases.

Is this a feature that is being added to future releases?

I appreciate your help

-Marcus
Are these SQL Server databases? If so you can create a view in one database that selects data from another - thus making it appear as though the data is all in the same DB.

Voila!

-Jamie|||lol... didn't think of that one...

Thanks :)|||Still onthe lookup subject, I have noticed that if I write a simpley query in the box "User results of an SQL query" say select * from DB1.dbo.tablea, DB2.dbo.tableb, I can then click the "Build Query" button and hey presto both tables are then available for me to work with even thought they are from different databases. I do notice that the top left corner of the table boxes have an arrow.

However if I go straight to "Build Query", add my first table by right clicking and selecting "add table" there is no arrow in the top left corner of the table box. I can then modify the sql statment manually to include the table from the other database. This table then appears in the top window with an arrow in the top left corner of it's box.

Is this a bug? should there actually be an optin to add a table from an alternative database and it's missing? As detailed above I can manually add the tables and the tool recognisines them.

Has anyone else seen this? Has this been fixed in later builds? I'm using Junes.

Thanks|||

What you have observed is not a bug.

We do supply an option to add tables. But we only list tables in the current database context. Tables in other databases have to be added manually. This is the behavior in June CTP and are not changed since then.

|||

Do you know if this will be changed in future CTP's?

Thanks

|||

I do not think so.

But please feel free to open a DCR via BetaPlace.

|||What about MS Access using DAO? In DAO you use something like this.

CDaoRecordset rset(&db);
rset.Open(dbOpenSnapshot, SQLquery, dbReadOnly);

This does not allow for binding to more than one database at the time. Therefore, how can you do a join query between two tables residing in different databases?

Thank you.

Lookup Transformation with parameter

Has anyone else noticed this? I want to be able to use a paremter in my reference table of my Lookup Transformation. I couldn't find any way for the dialog to accept SQL with a parameter so I checked on MSDN How to: Implement a Lookup Using the Lookup Transformation and sure enough in the article is says to click on the Parameter button. I don't have a Parameters button on this dialog. Error? Is this possible?

6. In the Lookup Transformation Editor, on the Reference Table tab, select a connection manager in the Connection manager list, and then do one of the following:

Click Use a table or a view, and then select either a data source view, a data source reference, or an OLE DB connection manager.
Click Use results of an SQL query, and then build a query in the SQL Command window, or click Build Query to build a query using the graphical tools that the Query Builder provides. Alternatively, click Browse to import an SQL statement from a file.
If the query includes parameters, click Parameters to map parameters to variables. For more information, see How to: Map Query Parameters to Variables in Data Flow Components.
To validate the SQL query, click Parse Query.
To view a sample of the data that the query returns, click Preview.

Lookup supports Parameters in partial cache mode. Pls go to the "Advanced" page in Lookup custom UI, enable memory restriction and enable caching, choose to modify Sql command so as to enable the "Parameter" button. See

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/f3395c65-0320-47f9-8d83-daaa082d8713.htm

Thanks

Wenyang

|||

Unfortunately, we haven't got to support parameters in the reference query on the Reference page. As Wenyang said you can use them in the query used for caching on the Advanced page.

Thanks.

|||

The problem I'm running into is that I want to be able to restrict the lookup based on a field in the reference table. When I add the SQL parameter in the Advanced page I get a validation error regarding the parameter in the ParameterMap not being in the availaible input columns. The parameter can't be in the input columns since it is only in the reference table.

I don't know if there is going to be a way around this so I am going to look into alternative ways to acomplish what I need.

Thanks for the feedback.

|||

Hi Infrandom

Is this still a problem for you or have you solved it? If not, let me know and I will post the solution.

Jays :-)

|||

Hi Jays,

I was able to get around my particular issue by changing my dataflow, however, if you've actaully found a way to add a parameter to the SQL Query of the reference table I'd be interested in learning how.

|||

Hi Infrandom

Sorry for the delay, I can't seem to get email alerts working with the MSDN forums, so I had to keep checking back for your answer.

I used parameterised lookups to find surrogate keys for facts during a historical load. That is, find the correct surrogate key where:

[Natural_Key] = [Dimension].[Natural_Key] AND [DateTimeStamp] >= [Dimension].[Start_DateTime] AND [DateTimeStamp] < [Dimension].[End_DateTime]

To achieve this you must perform the following steps:

1. Enter the query to return the reference table as per normal.

2. In the paramter mapping, link DateTimeStamp to either Start_DateTime or End_DateTime as if you were trying to do a standard 'equality lookup'.

3. Go into the advanced section and change the SQL there to look as follows:

select * from
(select * from [dbo].[Lookup_Table]) as refTable
where [refTable].[Natural_Key] = ? and [refTable].[Start_DateTime] <= ? AND [refTable].[End_DateTime] > ?

4. Hit the parameters button and map the parameters as follows:

{Param0 = Natural_Key, Param1 = DateTimeStamp, Param2 = DateTimeStamp}

And thats it! :-)

Just a couple of notes:

* If you don't create the dummy link described in step 2, the lookup will complain with some nonsense error.

* Before doing step 3, you will have to enable memory restrictions, etc.. before SSIS will allow you to enter the SQL statement.

I hope this helps, any Qs please feel free to ask.

Jays :-)

Lookup Transformation with parameter

Has anyone else noticed this? I want to be able to use a paremter in my reference table of my Lookup Transformation. I couldn't find any way for the dialog to accept SQL with a parameter so I checked on MSDN How to: Implement a Lookup Using the Lookup Transformation and sure enough in the article is says to click on the Parameter button. I don't have a Parameters button on this dialog. Error? Is this possible?

6. In the Lookup Transformation Editor, on the Reference Table tab, select a connection manager in the Connection manager list, and then do one of the following:

Click Use a table or a view, and then select either a data source view, a data source reference, or an OLE DB connection manager.

Click Use results of an SQL query, and then build a query in the SQL Command window, or click Build Query to build a query using the graphical tools that the Query Builder provides. Alternatively, click Browse to import an SQL statement from a file.
If the query includes parameters, click Parameters to map parameters to variables. For more information, see How to: Map Query Parameters to Variables in Data Flow Components.
To validate the SQL query, click Parse Query.
To view a sample of the data that the query returns, click Preview.

Lookup supports Parameters in partial cache mode. Pls go to the "Advanced" page in Lookup custom UI, enable memory restriction and enable caching, choose to modify Sql command so as to enable the "Parameter" button. See

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uirfsql9/html/f3395c65-0320-47f9-8d83-daaa082d8713.htm

Thanks

Wenyang

|||

Unfortunately, we haven't got to support parameters in the reference query on the Reference page. As Wenyang said you can use them in the query used for caching on the Advanced page.

Thanks.

|||

The problem I'm running into is that I want to be able to restrict the lookup based on a field in the reference table. When I add the SQL parameter in the Advanced page I get a validation error regarding the parameter in the ParameterMap not being in the availaible input columns. The parameter can't be in the input columns since it is only in the reference table.

I don't know if there is going to be a way around this so I am going to look into alternative ways to acomplish what I need.

Thanks for the feedback.

|||

Hi Infrandom

Is this still a problem for you or have you solved it? If not, let me know and I will post the solution.

Jays :-)

|||

Hi Jays,

I was able to get around my particular issue by changing my dataflow, however, if you've actaully found a way to add a parameter to the SQL Query of the reference table I'd be interested in learning how.

|||

Hi Infrandom

Sorry for the delay, I can't seem to get email alerts working with the MSDN forums, so I had to keep checking back for your answer.

I used parameterised lookups to find surrogate keys for facts during a historical load. That is, find the correct surrogate key where:

[Natural_Key] = [Dimension].[Natural_Key] AND [DateTimeStamp] >= [Dimension].[Start_DateTime] AND [DateTimeStamp] < [Dimension].[End_DateTime]

To achieve this you must perform the following steps:

1. Enter the query to return the reference table as per normal.

2. In the paramter mapping, link DateTimeStamp to either Start_DateTime or End_DateTime as if you were trying to do a standard 'equality lookup'.

3. Go into the advanced section and change the SQL there to look as follows:

select * from
(select * from [dbo].[Lookup_Table]) as refTable
where [refTable].[Natural_Key] = ? and [refTable].[Start_DateTime] <= ? AND [refTable].[End_DateTime] > ?

4. Hit the parameters button and map the parameters as follows:

{Param0 = Natural_Key, Param1 = DateTimeStamp, Param2 = DateTimeStamp}

And thats it! :-)

Just a couple of notes:

* If you don't create the dummy link described in step 2, the lookup will complain with some nonsense error.

* Before doing step 3, you will have to enable memory restrictions, etc.. before SSIS will allow you to enter the SQL statement.

I hope this helps, any Qs please feel free to ask.

Jays :-)

lookup transformation validation error

below is the error message:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow ACH Validate File and Header Info [Lookup ACH Batch Number [506]]: input column "ID" (571) and reference column named "BANKBATCHNBR" have incompatible data types.

"Lookup ACH Batch Number" is the name of the lookup transformation. input column "ID" has a string data type. reference column "BANKBATCHNBR" has a varchar(50) data type. can someone please tell me how i should go about debugging this validation error? thanks.

What is the length of the input column? I think (but may be wrong) that the lengths have to be the same.

There is a property of the LOOKUP transform that contains an XML snippet. I *think* that snippet contains the metadata of the lookup cache. I'm afraid I can't be absolutely certain about these things (and I can't remember the name of the property) because I don't have a BIDS instance to hand.

-Jamie

lookup transformation validation error

below is the error message:

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow ACH Validate File and Header Info [Lookup ACH Batch Number [506]]: input column "ID" (571) and reference column named "BANKBATCHNBR" have incompatible data types.

"Lookup ACH Batch Number" is the name of the lookup transformation. input column "ID" has a string data type. reference column "BANKBATCHNBR" has a varchar(50) data type. can someone please tell me how i should go about debugging this validation error? thanks.

What is the length of the input column? I think (but may be wrong) that the lengths have to be the same.

There is a property of the LOOKUP transform that contains an XML snippet. I *think* that snippet contains the metadata of the lookup cache. I'm afraid I can't be absolutely certain about these things (and I can't remember the name of the property) because I don't have a BIDS instance to hand.

-Jamie

Lookup transformation using effective dates

Hi,

I need to perform a lookup based on a business key and a date in the source that must be between an effective from and effective to date in the reference table. I've been able to achieve this by updating the SQL caching command on the advanced tab but the performance is very slow. I have 6 lookups of this type in the data flow with a source SQL statement that returns approx 1 million rows and this package takes over 90 minutes to run.

The caching SQL command now looks like this

select * from
(select * from [ReferenceTable]) as refTable
where [refTable].[Key] = ? and ? BETWEEN [refTable].[StartDate] AND [refTable].[EndDate]

and I've set up the parameters so that the business key is the first parameter and the source date is the second.

I have another lookup in the flow that does a straight equality comparison using 2 columns and the Progress tab shows that this lookup is cached (even though I haven't enabled it on the Advanced tab of the transformation editor) but none of the other lookups (using the date) appear to be cached, even though I have enabled them to be.

Can anyone suggest how I can improve the performance?

Thanks.

Hi,

When u use 'caching SQL command', caching can be either partial or none. In the 'none' mode each time it will execute the sql command for input. In the 'partial' mode, it will only cache the previously executed sql command results, so it won't cache any data at the outset.

Other alternative approach is, join ur input with the [reference table] using the key (don't use date). U will give multiple records. Use a conditional split to compare the date with start and end date. The output will be what u want.

|||

Thanks for the tip. Initially, the caching was partial so I would have expected the lookup speed to increase as the process went on ,as more and more of the target reference records were loaded, but this didn't seem to be the case.

I've now changed the package so that it joins directly onto the reference table and the speed has increased dramatically.

Thanks.

Lookup Transformation question

Hello,

I have a table like this:

Customer	Group
A	10
B	20

I need to do a translation of "group" to “local group”:

Customer	Group	Local Group
A	10	11
B	20	21

When a match is found, the group code should be replaced by the local group code ... but, when no match is found the group code should stay.

Is there any way to do this using Lookup ? I tried but when you set the error output to ignore it replaces the value that has no match with a NULL value. Maybe there is another way to get this done with or without the lookup component ?

The lookup transform does not 'replace' columns; it adds aditional columns to the data flow; and yes, when a match is not found and the error output is set to ignore error; it will put NULL in the new column(s). What I would do is to use a derived column after the lookup tarnsform. The Derive Column would have a column as "replace group" with an expression like:

ISNULL(LocalGroup) ? Group : LocalGroup

This expression is trying to replace the value of group only when Local group is not null

I hope this helps you

Rafael Salas

|||you could use the lookup transformation with a sql command to provide the result set.

select
c.Customer
, c.Group
, isnull(g.localgroup, c.group)
from yourcustomertable
left outer join yourgrouptable g
on c.customer = g.customer

is psuedocode that would do what you need (assuming this is how your tables are joined)

it will select the customer and group from the customer table, and the left outer join will force NULL for localgroup where you dont get any data returned from the join.

wrapping ISNULL around the localgroup will replace those nulls (due to not having a localgroup) with the customergroup|||Thanks for your answers, I'll try them out and see what works best for me.

Lookup transformation problem

Hello all,

I needed to lookup some table values based on a join of two fields...

I've configured the lookup transform to get the values via a SQL statement to minimize loading time.

However, when creating the relationships between the input columns and the lookup columns I receive following error:
input column [BATCH_ID] has a datatype which cannot be joined on

I've checked both input and lookup columns, both are of type DT_R8... Both columns in the different tables do have the same datatypes

Any idea how to solve this problem?

Thanks in advance

DT_R8 is a floating point data type, it is an approximate value which means it cannot be joined on. Try a decimal data type instead.|||

Hello,

Thanks for your reply but how can I change datatypes fetched directly from a SQL statement in the lookup query?

|||

lookup supplied many functions can cast datatype

i think you also can use convert or cast in sql statements

|||

Hello,

I've used Dataconversion and SQL functions to perform the requested conversions...

works fine however when executing the package it fails when loading the cache of the lookup components...
this is the message I get from the progress window:

[GET HOLDING_ID [2998]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E07 Description: "ORA-01722: invalid number ".

and

next line:

[GET HOLDING_ID [2998]] Error: OLE DB error occurred while populating internal cache. Check SQLCommand and SqlCommandParam properties.

However, I never changed the SQL command of the transform in the advanced tab...

Any Idea?

Thanks in advance

Lookup Transformation Join Column Types

Does anyone have a clue as to why DT_R4, DT_R8 are not allowed as join columns? This means I cannot join tables on AccountNumbers, InvoiceNumbers, etc. What a pain...

We didn't include support for floating point joins, because of the inherent inability of computers to accurately compare two floating point numbers.

To make the comparisons consistent, we would need to introduce some sort of rounding and user specified precision. If we did not, you may might be now posting a question about why 1.75 is not equal to 1.75 J

I hardly every see people store invoice or account numbers as floating point, I would be interested to know why you made this choice.

|||

>>inability of computers to accurately compare two floating point numbers

I had forgotten about that little fact.

I do not store account numbers and other similar keys as a floating point data type. However, the access database that I am importing with SSIS was created (by my predecessor) using double as the type for AccountNumber, rather than long integer. From here on out, I will do a data conversion before I do the look up and that should take care of my problem.

Thanks!

Lookup Transformation Fails on Oracle

Hi,

I am trying to use a lookup in a package and check for some conditions. On the advanced tab, I am trying to modify the condition from = to <=. But the same doesnt work when the target is on oracle, but the same works fine on SQL Server and DB2.

Any idea regarding the same?

Thanks,

Manish Singh

Can you execute the SQL query against the Oracle database using SQL*Plus or another similar query tool? It may be possible that Oracle does not support the specific syntax you're trying to use; since it's not included in your post it is difficult to say.

Can you post the modified SQL statement here?

|||

Yes, the query gets executed against oracle database after passing some defalt values. Below is the modified sql which I am changing on the advanced tab. The same works for SQL Server and DB2

select * from

(SELECT A_KEY, KEY_ID, SOURCE_ID, EFFECTIVE_FROM_DT, EFFECTIVE_TO_DT, EFF_START_DT, EFF_END_DT

FROM TABLEA) as refTable

where [refTable].[SOURCE_ID] = ? and [refTable].[KEY_ID] = ? and [refTable].[EFFECTIVE_FROM_DT] <= ? and [refTable].[EFFECTIVE_TO_DT] >= ? and [refTable].[EFF_START_DT] <= ? and [refTable].[EFF_END_DT] >= ?

The default condition generated is as follows:

select * from

(SELECT A_KEY, KEY_ID, SOURCE_ID, EFFECTIVE_FROM_DT, EFFECTIVE_TO_DT, EFF_START_DT, EFF_END_DT

FROM TABLEA) as refTable

where [refTable].[SOURCE_ID] = ? and [refTable].[KEY_ID] = ? and [refTable].[EFFECTIVE_FROM_DT] = ? and [refTable].[EFFECTIVE_TO_DT] = ? and [refTable].[EFF_START_DT] = ? and [refTable].[EFF_END_DT] = ?

|||Thanks for the additional information, although I'm not sure what to tell you. It looks fine to me, so I do not know why Oracle doesn't like it.

Lookup Transformation fails on DT_STR (3) string match

The Lookup Transformation fails to match this datatype when full caching is enabled. When partial caching is activated (Edit > Advanced, Enable Memory Restrictions > Enable Caching) the lookup works.

This appears to be a bug to me.

By chance is it comparing against a CHAR field as opposed to a VARCHAR field?|||Yes, the source field was a varcahr(3) and the lookup field was a char(3). However, I modified the lookup field to be a varchar(3) with the same results. One note: this field is currently storing a two-digit value (ISO Country code, e.g. US). That is what originally led me to change it to a varchar(3) field originally. I have trimmed and converted until I have run out of options.|||

Phil,

What is the optimal data type set up for matching strings, i.e char or varchar, trim both prior, etc?

|||

I think the behaivior you guys described is alrady documented:

from http://msdn2.microsoft.com/en-us/library/ms141821.aspx

Integration Services and SQL Server differ in the way they compare strings. If the Lookup transformation is configured to use full precaching, Integration Services does the lookup comparison in the cache; otherwise, the lookup operation uses a parameterized SQL statement and SQL Server does the lookup comparison. This means that the Lookup transformation may return a different number of matches from the same lookup table depending on the cache type.`

And from connect: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126618

Rafael Salas

Lookup transformation based on a csv file

My lookup data is in a csv file, not a table. Is there a way to get the Lookup transformation to use the csv file as the source 'table'? Obviously the alternative is to load the file into a SQL Server table and use that, but I want to keep it simple if possible.

The lookup component can only use OLE DB sources. You might be able to construct a query with OPENROWSET to read the text file via OLE DB, but that doesn't seem like the simple solution. You might also be able to use a Merge Join in Left Outer mode instead of a Lookup, which would let your source the right side from a flat file. You may have to add some sorting steps, though.
|||

The technique that I prefer for this situation is to have a separate package (or a separate data flow within the same package) that loads the lookup data from the source CSV into a lookup table in SQL Server (or whatever is your RDBMS of choice) and then perform the lookup in the "main" package against the database table version of the lookup data. If you ensure that the CSV to Table package is always executed first, this should solve the problem neatly.

Although this is not quite as simple as the ideal situation where the Lookup transform supported non-OLE DB data sources, it is much simpler (and much better performing) than any other alternative that I can think of.

Lookup transformation

Hi Everyone,

I'm trying to perform a lookup transformation. But the deal is, I have this one value that I am passing into the transformation, but I would like to gather all values that match the value I put in....does the lookup transformation do this? I tried it, and it appears as if it only returns one value for the one input. After the lookup, I have an access OLE DB destination setup...so I can capture all those values that corresponds to that one value I passed into the lookup. Does anyone have any ideas on how I can go about this?

Thanks!

Not quite sure what you want to do here. The LOOKUP works on a row at a time in isolation from all other rows.

Do you mean you want to return multiple values from your lookup cache? it doesn't do that, it only returns one value. The first one that it sees.

It sounds like you want to be looking at the MERGE JOIN component.

-Jamie

Lookup Transformation

Is there a way to ignore the unmatched rows using Lookup without creating another destination (Error Transformation for red connector)?Sure thing. Once the mappings have been defined in the lookup, click on the "Configure Error Output..." button and set the Error for Lookup Output to "Ignore failure." Then don't hook up the red flow to anything. Just leave it be.|||

shafiqm wrote:

Is there a way to ignore the unmatched rows using Lookup without creating another destination (Error Transformation for red connector)?

Yes, Just use 'Ignore failure' in the error output...the unmatched rows will go into the green output having null in the looup columns

|||

Rafael Salas wrote:

shafiqm wrote:

Is there a way to ignore the unmatched rows using Lookup without creating another destination (Error Transformation for red connector)?

Yes, Just use 'Ignore failure' in the error output...the unmatched rows will go into the green output having null in the looup columns

And if you truly need to ignore the error (unmatched records) then use the red flow and add it to a row counter. That way, you can capture the number of rows and they'll end up being separated from the valid records.

Lookup transform not finding blank match

I am having problems with a lookup transformation. I have a row in my lookup table for blank ('') source data. If I test the join using SQL the match is made, but the Lookup transform doesn't consider it a match and sends it to error output. Is there a property that I don't have set correctly or something else I am forgetting?

Check that you are not using empty string lookup against spaced "empty" strings or vice versa.

|||

I created the lookup table (dimension) and prepared the source table so I do know they are both empty strings.

Let me give a little more information. I have a package that runs prior to the fact table load (lookups). The package runs through the source and adds any dimension records that are not present. The problem arises when the lookup encounters an empty string from the source and it is unable to match on the empty string record already in the dim table, so it sends the record to the error output to be written to the dim table. But the write fails because the field is a primary key in the dim table and because the record exists it raises an error. In other words the Lookup transform can't see the empty string record, but the Write Transform can.

I hope that this explanation is understandable.

Dave

|||

Dave,

I've noticed when I pull back a string from tables, the string is padded with spaces to the full size of the field.

Try two things to see if one of these might be your problem:

1) After you pull in your columns from the source, add a derived column that concatenates a single character (like 'a') before and after the column that is return the empty string. Use a data viewer after this column, to see if the column looks like this: 'aa' or this 'a a'.

If it's the latter, change your derived column to trim the string, or trim the column when pulling it from the source.

2) In your initial lookup where you are trying to see if the empty string ('') exists, try using a SQL statement that selects the columns you are looking up. In your SQL statement, concatenate a single character in the same manner as step 1 to the empty string column and add it to your output. Then use a data viewer after this column to see what the column looks like.

If it's 'a a', trim your column in your SQL statement before returning it.

Good luck!

Jessica

|||

Like Jessica said, use the dataviewer to check the data.

SSIS is using .net string compare on strings during lookup process, which think '' and ' ' is different, but sql is ignoring trailing empty spaces during select joins.

If your oledb command insert statement check if the data exist before insert, then you may always insert ' ' into the database but always ignore the ''.

|||

Check out

http://blogs.conchango.com/kristianwedberg/archive/2006/02/22/2955.aspx

especially the comments at the end - you're not alone :-)

Cheers/Kristian

Lookup problem

I have a problem related to execution of a lookup in a transformation script in SQL server 2000 DTS package.

I have a lookup that launches a stored procedure:

EXECUTE TableID ?, ? output

This lookup is executed from a script like this:

Dim newID as integer
DTSLookups("GetNewID").Execute "string_data", newID

The problem is that the second parameter (it is an output parameter) is unchanged althghough it is changed in the stored procedure

Any suggestions ?

Thank you, Gabyx

The Execute method won't assign a value to newID. It returns a value, or array of values.

Try this:

Dim newID as integer
newID = DTSLookups("GetNewID").Execute "string_data", newID

Let me know if this works for you.

Lookup finds match on empty reference table

Hi all,

In BOL it says: "The Lookup transformation performs an equi-join between values in the transformation input and values in the reference dataset. Using an equi-join means that each row in the transformation input must match at least one row from the reference dataset. If there is no matching entry in the reference dataset, no join occurs and no values are returned from the reference dataset. This is an error, and the transformation fails, unless it is configured to ignore errors or redirect error rows to the error output. "

I have a lookup transformation which is supposed to find a match on two fields in the reference dataset (a table in my case) but strangely, when I execute my package and the reference table is empty the lookup still finds match for each row of my input dataset.

Does anyone have an idea why? I could'nt find anything about that in BOL.

Sbastien.

If the lookup is returning rows, yet now rows exists, it cannot be matching, the returned columns if any will be null. You can however allow this behaviour, by ignoring errors. A failed lookup match is classed as an error, and can be set under the Configure Error Outputs button/screen. See if you have set Ignore for errors.|||Indeed I have configured the error to "Ignore errors" but I thought it will simply avoid the package to fail if it couldn't find any match. I don't want to use Redirect rows either as I have nothing to do with the non matching rows.|||

Sebastian,

The solution here then is quite simple. Use a Conditional Split to filter out the columns that have NULL values in the lookup columns - thereby giving you the rows that yielded a match.

-Jamie

Lookup error redirection

Hi,

I would like to know if the following is possible or if there is another way to implement this. I have a lookup transformation which i check to see if a specific record is stored. If nothing is returned i would like to insert a record into the table and then try the lookup again so that this time it will find the record and continue processing. How could i redirect the data flow to allow something like this to be done. I have tried linking the lookup failure constraint to an oledb destination and then attempted to link this back to the lookup but this has not worked.

Does anyone have any ideas on this?

Thanks in advance,

GrantInstead of using an OLEDB Destination component, use an OLEDB Command component. The Destination component will terminate flow while the OLE DB Command will pass records through.|||I seem to have problems when i try to use this. Should it allow me to use a subquery which obtains a record count from a table and if that returns a 0 then insert values from the input into another table?

Thanks

Grant|||I generally use stored procedures as the SqlCommand property. [Syntax is EXEC dbo.StoredProcedureName ?, ?, ?] One of the reasons I like stored procedures is that on the Column Mapping tab, the parameter names are displayed and it's easier to verify that I have the correct data mapped.|||

Another solution is to use multicast and union components: split the error output of Lookup component, direct one output to Sql or OLEDB Destination component, then merge another output with successful output of Lookup component using Union All component.

Lookup -> Multicast -> Destination
| |
Union All
|

|||

Martin, would you mind explaining in a little more detail?

At present, I use the OLEDB Command quite frequently, but with SQLCommands like, "UPDATE tblTEMP SET TempID = ?, SSN = ?,... WHERE ID = ?" etc. As you mentioned, it's a pain to map all of the fields to generic param0, param1, etc.

So what you are saying is that I can create a SP to "map" the parameters?

I appreciate any advice that you can offer a newbie...

lookup - error msg

hi all,

good day!

i have dataflowtask_a and dataflowtask_b

i also have a send mail task

inside dtf_a i have a very long transformation which has 10 lookup task

each lookup task is configured to have an error output. should every lookup

encountered an error, the error output of the

lookup sends a unique error msg per lookup and is appended to a

string variable errorlst. Should dtf-a encounter an error it sets the variable

@.nogo=true.

In the control flow if @.nogo=false it proceeds to dtf_b otherwise it proceeds to

sendmail the consolidate error message.

Questions:

1. I only want to have one unique error message sent per lookup. if there are two

lookup error it should send only one message

2. the message should look like this: "error lookup1 : error lookup2 :and so soon"

how can i do this

thanks!

joey

.

I assume you are using the Script component to append messages to your errorlst variable. If you do, then it should not be a problem to append only the first lookup error.

Thanks.

|||

yeah i'm using the script component

can anyone please post a script for

the script component on how to append on a

message on a variable @.errlst with package scope

thanks