Multiple Lookups

• robin.pryor

  SSCrazy

  Points: 2583

  More actions

  February 9, 2011 at 10:01 am

  #388818

  I'm relatively new to SSIS, but very familiar with old school DTS. So I've discovered the lookup transformation, but have a couple questions.

  Here's my structure (minus the irrelevant stuff):

  CREATE TABLE [dbo].[stgClients](

  [clients_id] [nvarchar](254) NULL,

  [division_code] [nvarchar](254) NULL, -- maps to a value in Divisions

  [lastuser] [nvarchar](254) NULL, -- maps to a value in Staff

  [status1] [nvarchar](254) NULL, -- maps to a value in lkpClientStatuses

  [status2] [nvarchar](254) NULL, -- maps to a value in lkpClientStatuses

  [status3] [nvarchar](254) NULL, -- maps to a value in lkpClientStatuses

  [zipcode] [nvarchar](254) NULL, -- maps to a value in lkpZipCodes

  [data_source] [nvarchar](50) NULL, -- maps to a value in lkpDataSources

  [intDivisionId] [int] NULL, -- from Divisions based on division_code match

  [intLastUserId] [int] NULL, -- from Staff based on lastuser match

  [intStatus1Id] [int] NULL, -- from lkpClientStatuses based on status1 match

  [intStatus2Id] [int] NULL, -- from lkpClientStatuses based on status2 match

  [intStatus3Id] [int] NULL, -- from lkpClientStatuses based on status3 match

  [intCountyId] [int] NULL, -- from lkpZipCodes based on zipcode match

  [intRegionId] [int] NULL, -- from lkpZipCodes based on zipcode match

  [intStateId] [int] NULL, -- from lkpZipCodes based on zipcode match

  [intDataSourceId] [int] NULL

  ) ON [PRIMARY]

  I've figured out how to do 1 lookup transform, but I have 2 questions here...

  1. how do I include multiple lookup transformations in the data flow?

  2. how do I just return a null if there's no match?

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  February 9, 2011 at 10:12 am

  #1284621

  Drag another lookup to the screen, and bring the datastream (green/red arrows) to it to do your next lookup. The previously defined lookup gets added to the metadata of the stream from that point forward.

  For doing left join lookups, you want to go into the error button on the bottom left of the lookup definition, and choose 'ignore failure'. This will allow null values into the item.

  ---

  - Craig Farrell

  Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

  For better assistance in answering your questions[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA