Hi @Winston TheFifth ,
Here is two resolutions to meet your need.
First one we could use lookup-transformation.
Note that the reference dataset can be a cache file or an existing table. The Lookup transformation uses either an OLE DB connection manager or a Cache connection manager to connect to the reference dataset.
I'd suggest that you may import the reference csv file to SQL Server database via SSIS first.
And then you may use lookup transformation successfully.
The second one is that we may use merge-join-transformation.
Here is a blog which provide the detailed steps.
ssis-basics-using-the-merge-join-transformation
Say for example source is FlatFile, and Look up CSV File is FlatFile B.
After FlatfileSource A have a sort transformation, similarly after FlatFileSource B(CSV File For Look UP) have a sort transformation.
Sort both the transformation based on the column for which you wanted Look up.
So you will have two inputs now, one from Sort Transformation A and from sort transformation B. Give these two inputs to Merge Join Transformation. And use inner join Option.
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.