SSIS - how to use lookup to add extra columns within data flow

1
votes

I have a csv file containing the following columns:

Email | Date | Location

I want to pretty much throw this straight into a database table. The snag is that the Location values in the file are strings - eg: "Boston". The table I want to insert into has an integer property LocationId.

So, midway through my data flow, I need to do a database query to get the LocationId corresponding to the Location. eg:

SELECT Id as LocationId FROM Locations WHERE Name = { location string from current csv row }

and add this to my current column set as a new value "LocationId".

I don't know how to do this - I tried a lookup, but this meant I had to put the lookup in a separate data flow - the columns from my csv file don't seem to be available.

I want to use caching, as the same Locations are repeated a lot and I don't want to run selects for each row when I don't need to.

In summary:

  1. How can I, part-way through the data flow, stick in a lookup transformation (from a different source, sql), and merge the output with the csv-derived columns?
  2. Is lookup the wrong transformation to use?
3
sql-servervisual-studio-2008ssis
Lookup is fast enough and without caching. You don't need caching. If LocationID and LocationName are from the same file, you need 2 dataflows and 2 tables. In first you gonna insert LoactionName and LocationID (OR generate LoacationID) in 1 table. In second dataflow you gonna insert lookup element and get the LocationID by LocationName and everything insert into second table. - Justin
Hmm. The problem is that Location Name is from a file, Location Id is from a a database. I can't lookup id in the database until i have read the name from the file. - Kev
Hm so you need first to merge that LocationID with LocationName... strange architecture you using - Justin
No, I need to lookup an additional value using one of the input columns. - Kev

3 Answers

3
votes

Lookup transformation will work for you, it has caching and you can persist all input columns to the output and add columns from the query you use in lookup transformation.

You can also use merge join here, in some cases it is better solution, however it brings additional overhead because it requres sorting for its inputs.

1
votes

Check this.

Right click on look up transformation -> go to show advanced editor -> go to Input and output properties.

here you can add new column or you can change data type of existing columns. for more info how to use look up Click Here

0
votes
  1. Open the flat file connection manager, go to the Advanced tab.
  2. Click "New" to add the new column and modify the properties.
  3. Now go back to the Flat File Destination output, right click > Mappings > map the lookup column with the new one.