question

UnknownUser avatar image
0 Votes"
UnknownUser asked ZoeHui-MSFT edited

Map Two sources to the destination SQL DB table

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

148280-image.png


Azure SQL DB Customer table

148238-image.png


All fields from the flat file is mapped to the Azure SQL DB table, on the insert I need to map the RoomID field to 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 matches


sql-server-integration-services
image.png (5.3 KiB)
image.png (4.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT edited

Hi @UnknownUser,

You may use OLE DB Command to meet your need.

Code like below.

 update [RM_Room]
 set [RM_Room].roomid=Customer.roomid
 from [RM_Room]
 inner join Customer
 on [RM_Room].name=Customer.location

148358-screenshot-2021-11-11-111133.jpg

[ole-db-command-transformation][2]

Regards,

Zoe


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.