Map two source to the destination SQL DB table-CodePudding

This is my first SSIS package any help is greatly appreciated. I have a flat file that needs to be inserted into the Azure DB. I have data from the flat file disassembled all the fields from this flat-file need to be mapped to the Azure DB table. The Flat file has the below fields like

Azure SQL DB Customer table

All fields from the flat file are mapped to the Azure SQL DB table, on the insert, I need to map the RoomID field to a different table called RM_Room

SELECT [ROOMID]
      ,[NAME]
  FROM [dbo].[RM_Room]

when the Name on the [RM_Room] matches the Location field on the Customer. I am not sure how to lookup on the RM_Room table for every customer insert and bring the Room id when the fields match

CodePudding user response：

You need to use a lookup transformation to get the relevant ROOMID. Also, you can use a fuzzy lookup if different string may have the same value.

Please refer to the following article for more information:

SSIS Lookup transformation vs. Fuzzy Lookup transformation