April 8, 2008 at 6:27 am
Hello again. This is a weird one that may not be possible, but I thought I'd ask anyway. @=)
I have a bunch of ContractIDs with NULL customer names. I want to output this to a file to send to our Business Unit to correct. Unfortunately, because ContractID is an identity field, the BU won't recognize it. They want a ContractNumber (different field) associated with the records.
I have a LOOKUP transformation sending the valid names to my data warehouse, but I'd like to use the other part of the LOOKUP to send the invalid data to a text file. Unfortunately, I can't quite figure out how to go back and pick up the ContractNumber for only the invalid records. I don't want to pull in ContractNumber for the valid ones.
Any thoughts on how I could do this?
Thanks in advance!
April 8, 2008 at 6:47 am
Either I am not following, or there are two really obvious answers.
The first is to include the invoice number in your data set for the good and bad records and just exclude it in the output of the good records - you don't have to include all columns in any type of output.
The second would be to use another lookup component on the error output of your current lookup to get the invoice number from your identity value.
April 8, 2008 at 7:34 am
Michael,
You've almost got it.
I don't want to use option 1. I have too many records and I want to keep the column width down to what's necessary. However, on option 2, here's where I get stuck.
StagingTable OLEDB source goes to Lookup1 to see if Customer records exist in non-Staging table. Lookup1 then goes to ConditionalSplit to separate the records by ValidNames or NULLNames. The ValidNames split goes to the Destination OLEDB and loads the records.
So, I already have the records that don't work in the other output of my ConditionalSplit. I suppose I could send them to another Lookup, but Lookup doesn't give me the option of adding a column from a table in another database. Unless I'm totally missing something here.
Did I just re-confuse you?
April 8, 2008 at 7:46 am
Try a merge join operation. You'd have to add the other table from the other database as a new source.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 8, 2008 at 7:47 am
Yup - more confused.
The point of a lookup is to get a field from another data source by a matching field, so I do not understand what you are saying.
Also, why do you have a conditional split? From your current lookup, if you find a record it will come out of the lookup component successfully. If there is no record, your data will come out of the lookup error output if you just set the errors to redirect rows. I do not see why you have a conditional split afterwards unless you are just trying to avoid using a lookup error output.
Sorry, I don't think I am going to be much help on this one.
April 8, 2008 at 7:50 am
Actually, I think Matt just hit it. I don't know why I couldn't see that. It's sitting right in front of me.
DOH!
Thanks, Matt. I think that'll work.
As far as the conditional split goes, I wanted an error output for records that didn't meet either condition as a "Very Screwed-up Data" check.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply