0

I'm using SQL Server Data Tools in Microsoft SQL Server 2012 to load data from the staging to the data warehouse. During the ETL process, I use the Lookup Transformation to get the dimension key from the lookup table into my fact table. My issue is that when I use the Full Cache in Lookup Transformation, all the rows went to the no match output. When I use Partial cache or No cache, all the rows went to the match output as is supposed to be. I'm really confused and don't understand what's going on here. I really need some help here.

Thanks, Dan

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3633617
  • 1
  • 1
  • 2
    Are you aware that your lookup casing is dependent upon your cache lookup type? Also, the nullability matching might also be affected by the full vs non-full type. Bingle this to verify I'm not totally mad – billinkc May 13 '14 at 19:22
  • [Lookup Pattern: Case Insensitive](http://blogs.msdn.com/b/mattm/archive/2008/11/23/lookup-pattern-case-insensitive.aspx) [Lookup cache modes](http://blogs.msdn.com/b/mattm/archive/2008/10/18/lookup-cache-modes.aspx) [SSIS: Case-sensitivity in Lookup component](http://consultingblogs.emc.com/jamiethomson/archive/2008/02/12/SSIS_3A00_-Case_2D00_sensitivity-in-Lookup-component.aspx) [SSIS – Lookup Transformation is case sensitive](http://sqlblogcasts.com/blogs/jorg/archive/2008/02/12/SSIS-_1320_-Lookup-Transformation-is-case-sensitive.aspx) http://stackoverflow.com/questions/7233766/ – billinkc May 13 '14 at 22:30

1 Answers1

1

If you are looking up based on a VARCHAR or NVARCHAR field, as billinkc has suggested, if the fields are in different cases (Dan Vs dan) this would lead to a no match. Try doing an derived column of UPPER(SourceColumn) and use the query in the lookup Transformation to Select UPPER(MatchingColumn), LookedupKey from LookupTable and match on this.

TMNT2014
  • 2,102
  • 1
  • 11
  • 13