SqlShare.com Logo
 
Skip Navigation Links
Home
Channels
Classes
About Us
Login / Register
Subscribe to the All channels feed  Goto the SqlShare.com blog
Image of Tim Mitchell
Author:

Author Bio:
Tim Mitchell is a Microsoft SQL Server database developer, business intelligence consultant, writer, and speaker. He has been working professionally with SQL Server for over six years, and holds the MCTS and MCDBA certifications from Microsoft as well as a Bachelor's degree in computer science from Texas A&M University - Commerce.
SSIS Lookup Transform - Lookup Failures

SSIS Lookup Transform - Lookup Failures

In an earlier video Tim gave a nice explanation of how to use the Lookup Transform, in this video he shows you how to deal with errors - cases where you have a state named 'Forida' that doesn't match to your lookup table.

Video Information
Publish Date:
April 15, 2009
Length:
3:40
Skill Level:
100
Rating:
4.31 out of 5

You must be logged in to view this video.    

Bookmark and Share

  Rate This Video:  
 
1=Poor, 3=Good, 5=Excellent
Comments:
surya kiran on 5/21/2009
Helpful, thanks

Miguel on 6/2/2009
Very Good.

carollin on 6/19/2009
thank you..great video...I have to look up gl account dimension table for gl account skey, when it is not found, i want to still load the fact but also add the new gl account to dimension table. hopefully you will be posting more training videos on this subject.

vishal on 7/5/2009
nice and simple to understand vedio

Allen on 7/17/2009
informative

Dinesh Pendlimarri on 7/21/2009
thank you!

Philip Hickey on 7/21/2009
Definitely the best presenter on Jumpstart....very well paced demo

Armando Caballero on 7/21/2009
very basic video

Rubens on 7/21/2009
Fantastic video, much appreciated. Volume is quite low though, you might want to look into this for future videos. Thanks!

Jamshid Nouri on 7/21/2009
excellent demo

Christian Bahnsen on 7/21/2009
Very helpful to an SSIS newbie like me.

Jamshid Nouri on 7/21/2009
excellent demo

cheryl on 7/22/2009
I did not see why 7 reocrds go to fail route clearly.

nilanaksha on 7/24/2009
Simple and good one to learn Lookup transform as well error handling

Mark Gorczyca on 7/28/2009
This is a good piece of information for people getting started with the Lookup Transform. One question I haven't seen to much on though, suppose that you don't want to capture the error output rows for any reason (writing to audit table, destination, etc.) SSIS will give you a validation warning that the error output is unused. What is the best practices approach if you don't want validation warnings ... I've started just sending the error output to a Row Count transform, and storing the count in a variable (which goes unused). I don't want to waste the resources of writing this data down to disk. Just a question in case you find the time to discuss down the road.

Abhijeet Srivastava on 7/28/2009
excellent

vin lawrence on 8/1/2009
Should have started off with the end point from the lookup basics video

Amy Darroh on 8/4/2009
Really enjoyed this one. Very informative yet easy to follow. Thanks!

Sujoy Bardhan on 8/28/2009
Looks good. But I was expecting if you could use a sub string function and then based on the substring put a value to it and finally use the value to multiply with another column. A good functional example which I can think of is : Lets have a column as 23.4M which could be a Share Issuesd. We use that column and take the right first character. If it's M then the value will be 1000,000 if its B then 1000000000 if L then 1000000. Lets call it a multiplier. Then use this multiplier to multiply with a column say Shares which could be numeric. I am new to SSIS and was looking for derived columns with multiple processes. That would be a help.

Tim Mitchell on 8/29/2009
Mark, regarding the validation warning for unused rows, there is a useful tool called the Trash Destination by Konesans. It's a third party add in that acts as a do-nothing destination, and is ideal for testing or other situations where you have data to be sent to the bit bucket.

Tim Mitchell on 8/29/2009
Sujoy, you could use an SSIS derived column for this purpose. The expression statement would likely be quite complex, but it could be done. I'll be glad to help you with this, if you want to contact me at tdmitch [at] gmail.

Rajasekhar on 9/5/2009
very good article

art on 9/11/2009
thanks, Tim, your video really helps.

ted on 9/18/2009
sound very poor

Dinesh on 10/7/2009
Excellent

sunil on 10/22/2009
good

murshitha21 on 10/30/2009
Great Example

absher786 on 11/4/2009
excellent work you guys. Keep it up. Thank you for this valuable free service.

Nate on 11/18/2009
very good for us new beginners. Just one question:The match output handles the rows in the transformation input that match entry in the reference dataset - what about in reverse? I want the output handles the rows in ref dataset that match the input rows ?

Tim Mitchell on 11/19/2009
Nate - good question. Actually the reference data set is not used as part of the data flow except for the purpose of lookups. If you wanted to do the reverse operation, you'd need to create a second data source, lookup, and data destination with your original lookup as the data flow stream.

TJA on 12/6/2009
I appriciate the presentaion. Very Good!!!

Rashmi Patankar on 1/18/2010
Can you add webcasts for fuzzy lookup as well?



Must Be Logged In

References
Supporting Code

Sponsored Ads
 

How Do I Become a Video Author? |  Newsletter History

Copyright © Fourdeuce, Inc., 2005-2009. All Rights Reserved | Privacy Policy | Terms & Conditions