| Author |
Topic |
|
Cry
Starting Member
24 Posts |
 Posted - 2009-01-23 : 09:45:02
|
| Hi,i have some trouble with a lookup transition and i hope someone can help me. What i have done until now:Input stream:kdnr (DT_WSTR)some more fields...lookup transition:SELECT Statement: SELECT KD_id, KD_Kdnr FROM NSC_KD ORDER BY KD_Kdnrrelation between kdnr and KD_Kdnrlookup field: KD_Id, add as new field, Alias: KDIDCache: modified Select-Statement: Select * FROM (SELECT TOP 1 KD_id, KD_Kdnr FROM NSC_KD ORDER BY KD_Kdnr) as reftable WHERE reftable.KD_Kdnr = ?Datatype KD_Id = Long Int (SQL Server 2005)Datatype KD_Kdnr = nchar (20) (SQL Server 2005)The NSC_KD table ist filled. All kdnr (of stream) are in the table NSC_KD. I checked the statements via SQL Management.The problem is the output of the lookup transition. KDID is allways NULL. The kdnr seems not to be found...Are there any other restrictions for the lookup transitions, thati have to consider?Thanks |
|
|
revelator
Starting Member
32 Posts |
Posted - 2009-01-23 : 10:30:15
|
| The lookup will be case-sensitive, could that be the problem?-----------------------------------Waiting for the Great Leap Forwards |
 |
|
|
Cry
Starting Member
24 Posts |
Posted - 2009-01-23 : 10:43:27
|
| unfortunately not, because in kd_kdnr and kdnr are only digits...also the datatypes of both columns are identic (both are DT_WSTR)...any other idea? |
|
|
|
revelator
Starting Member
32 Posts |
Posted - 2009-01-23 : 10:50:56
|
| When you click Preview on the lookup transform, do you see the values as expected?-----------------------------------Waiting for the Great Leap Forwards |
|
|
|
Cry
Starting Member
24 Posts |
Posted - 2009-01-23 : 10:52:57
|
| to simplify the situation i have changed the transformation:i added before a derived column transformation to a column named KDID with value 0 (DT_UI8)and changed the lookup transformation to:use table: NSC_KDreference: kdnr - KD_Kdnrlookup field: KD_Id, replace field KDID, alias: KDIDcache: noNow i get an error, that the search has found no match...any idea? |
|
|
|
Cry
Starting Member
24 Posts |
Posted - 2009-01-23 : 10:54:25
|
| preview shows the correct data... |
|
|
|
revelator
Starting Member
32 Posts |
Posted - 2009-01-23 : 10:56:50
|
| Try redirecting the rows to another transform rather than failing the package. You can then add a data viewer to inspect what rows are not matching the lookup.-----------------------------------Waiting for the Great Leap Forwards |
|
|
|
Cry
Starting Member
24 Posts |
Posted - 2009-01-23 : 11:01:33
|
| i checked this too. all rows are not matching.... |
|
|
|
revelator
Starting Member
32 Posts |
Posted - 2009-01-23 : 11:24:39
|
| Very odd! Can you ensure the values have no extra spaces at the end? By using TRIM in the Derived Column or by casting to an int and back again?-----------------------------------Waiting for the Great Leap Forwards |
|
|
|
Cry
Starting Member
24 Posts |
Posted - 2009-01-24 : 13:49:46
|
| columns are TRIMed by the select statement. i checked the select statements via SQL Management. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-24 : 13:58:07
|
| does the table NSC_KD exists only under single schema? |
|
|
|
Cry
Starting Member
24 Posts |
Posted - 2009-01-24 : 14:17:57
|
| yes. NSC_KD exists only in one schema... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-25 : 04:59:34
|
| then it seems like column values contain some unprintable characters. it may be a hard space (ASCII 160),carriage return, line feed,.... Try using query in query analyser with results in text option to spot them. also try using replace() and see if they can be replaced |
|
|
|
Cry
Starting Member
24 Posts |
Posted - 2009-01-25 : 05:32:59
|
| i checked this. i cutted all values via LEFT (KD_Kdnr,5) in the reference table as in the stream input. no rows match .... |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-25 : 12:18:50
|
| and tried values in lookup table also? |
|
|
|
Cry
Starting Member
24 Posts |
Posted - 2009-01-26 : 08:47:51
|
| yes, so that all values are of length 5 characters.i also checked a join via SQL Management. The join selects the correct data. Additionaly, the statement, that was automaticly genereted by SSIS, i have put into a stored procedure. When i run it via TSQL-script it also selects the correct data.What is going wrong with this lookup trans???? |
|
|
|
Cry
Starting Member
24 Posts |
Posted - 2009-01-26 : 09:43:19
|
| Can anybody tell me if there is a restriction on the DT_WSTR type?All examples i found, are use DT_STR. Are there special reasons for this? |
|
|
|
|
Problem with Lookup transformation