Huge issue with the Lookup problem

Huge issue with the Lookup problem

• jziemian

  Do you have a fixed maximum number of possible values (NO/OTH/OWN) that can occur in one record If yes, you can split the string like "NO, OWN" into several columns, lookup each column, then merge them back (with script component or derived column transform) into one string.
  
  

• F1rst_Kn1ght

  While the values are still in 3 columns, do 3 lookups in a row and then combine the resulting looked up columns.
  
  

• footballdirector

  No I don't have a fixed number of columns. That is another problem here. Every amenity could have different number of possibilities. So that adds to the complexity with this issue.
  

• noon1

  Jamie,
  
  Congratulation on becoming the MVP!!! You really deserve it....
  
  I have three columns what come to me with Yes/NO values in the pipeline. So I am concatenating them into one value as my destination has only one column for the 3 input columns. But before I actually dump in the data to the posting table, I need to do a lookup to figure out the description for the codes. So that is essentially what I am trying to figure out. How can I accomplish something like this using SSIS
  
  Thank you,
  Shiva
  
  

• Ovidiu Platon [MSFT]

  I don't quite understand how the LOOKUP can return any matches at all if your incoming row contains the string value "NO, OTH, OWN". Perhaps I'm not understanding.

  • Are these three values in different columns
  • Do you convert to 3 rows in your pipeline rather than the one that you start with

  -Jamie

  
  
  

• Massy

  yosonu wrote:

  Jamie, Congratulation on becoming the MVP!!! You really deserve it....

  Thank you very much. Its very nice of you to say so.

  yosonu wrote:

  I have three columns what come to me with Yes/NO values in the pipeline. So I am concatenating them into one value as my destination has only one column for the 3 input columns. But before I actually dump in the data to the posting table, I need to do a lookup to figure out the description for the codes. So that is essentially what I am trying to figure out. How can I accomplish something like this using SSIS Thank you, Shiva

  Can you not do the lookup prior to doing the concatenation

  -Jamie

  
  
  

• William Kirchhoff

  Here is a better explanation of my problem really.....

  Here is the problem I am facing currently with SSIS that I need help on ASAP

  My Source dataset looks like this

  WTR_NO WTR_OTH WTR_OWN

  ------------------------------------------------------------------

  1 0 0

  1 1 0

  Now, this is for a Water attribute that has values of 0 and 1 in the dataset above. My posting table contains

  WTR_DESC

  ------------------

  NONE

  NONE, OTHER

  The description for the codes NO, OTH, OWN etc comes from a lookup table that is structured like this

  Amenity_Code Amenity_Description

  ------------------------------------------------------

  NO NONE

  OTH OTHER

  OWN OWNED

  I have 100s of Amenities like Water, Appliance, Kitchen etc. So hard coding the values in something like a script task seems very cumbersome.

  So, is it possible to send in something like ‘NO, OTH’ as a variable and then generate my ‘NONE, OTHER’ after performing a look up Notice I have the final posting value as a concatenated comma separated dataset. Maybe a custom SSIS transformation task would be the best fit, but I don’t know how to write the custom tasks yet and I want to see if SSIS can do something like this for me with the existing tasks it has already in the transformation area.

  Thank you,

  Shiva

  
  
• Huge issue with the Lookup problem