?
Solved

Can the SSIS look up function be used for complex join or lookup?

Posted on 2012-08-19
1
Medium Priority
?
1,032 Views
Last Modified: 2012-09-04
Hi,

I have used tsql join in my ETL transformation, due to complexity and easy maintainence of my ETL script. I understand that SSIS look up function is good in terms of lookup for surrogate key from dimension table.

However, I got complex business rule where I had to translate my lookup table with customize view such as use of select statement to filter the row I need.

What is the best approach in terms lookup tsql or SSIS?

Thanks in advance
0
Comment
Question by:keplan
1 Comment
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 1500 total points
ID: 38311414
From my experience, just use the SSIS lookup, when you really need to, otherwise relay on the SQL engine to do the join and lookup for you.

Anyway The lookup in SSIS has 3 modes,

1-no cache  -- slower not case sensitive
2-Partial cache - case sensitive, will cache your values as it match.
3-Full cache   - case sensitive  - Faster - will cache your lookup table upfront.

You might also restrict the cache size if you'll use partial or full modes.. read this article about lookups...

http://blogs.msdn.com/b/sqlperf/archive/2007/04/24/getting-optimal-performance-with-integration-services-lookups.aspx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question