Lookup Pattern: Range Lookups

Lookup Patterns

SSIS Team Blog — Tue, 25 Nov 2008 20:33:27 GMT

From the Lookup presentation I put together for the MS BI conference in October, here is a series of

Processing late arriving facts in SSIS

SSIS Team Blog — Tue, 25 Nov 2008 20:41:07 GMT

This is just a place holder. Please see the post on different approaches of doing Range Lookups in SSIS

Learn more about Lookup and its new features: A compilation

Douglas Laudenschlager — Tue, 20 Jan 2009 01:22:34 GMT

The SQL Server Integration Services team added valuable new caching options (and scalability) to the

Explanation of the Script component code - Urgent

ravisharvindMSBI — Thu, 30 Jul 2009 19:50:38 GMT

Hi,

Can anybody explain in brief the code used in the script component?

I have a scenario to implement the same.

Any help would be greatly appreciated. Thanks

re: Lookup Pattern: Range Lookups

mmasson — Thu, 30 Jul 2009 22:44:41 GMT

Which part do you need explained?

Explanation of the Script component code

ravisharvindMSBI — Sat, 01 Aug 2009 23:47:20 GMT

Basically the part within the while loop.

Why is StartDate not considered in the code?

I am a starter, so please bear with me.

Thanks for your help.

re: Lookup Pattern: Range Lookups

mmasson — Mon, 03 Aug 2009 21:00:09 GMT

An optimization. It assumes that everything up to the next end date belongs to the previous record.

ex.

Key 1, EndDate 2001/01/01

Key 20, EndDate 2003/01/01

Key 40, EndDate 2008/01/01

Key 60, EndDate NULL

If a record with a transaction date of 1999 comes in, we can determine it belongs to Key 1. If a record with a transaction date of 2004 comes in, we know it belongs to Key 40.

Note, this will only work if there are no gaps in your history. It assumes that your data has been cleaned before hand.

Error Redirection

ravisharvindMSBI — Thu, 20 Aug 2009 21:55:57 GMT

Hi,

How do i redirect the lookup no match output in the script component if there is no dimension natural key available?

Can you please help me out with the code?

Thanks in advance for the help.

Error Redirection - Urgent

ravisharvindMSBI — Sun, 23 Aug 2009 18:19:35 GMT

How do i redirect the lookup no match output in the script component if there is no dimension natural key available?

Even if redirection is not possible i would like to replace the corresponding key of the record like 1, which is the key for default record in my dimension?

waiting for your response...

re: Lookup Pattern: Range Lookups

mmasson — Mon, 24 Aug 2009 17:41:21 GMT

You can redirect with the script by adding another output to the component.

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/66136/

Alternatively, you can replace the key value with 1 instead of throwing an exception like the script does now.

Not Working

ravisharvindMSBI — Mon, 24 Aug 2009 21:06:07 GMT

Hi Masson,

Thanks for the reply.

I tried replacing the key for the corresponding row to 1 instead of throwing the exception as you said. But it fails again. I don't want the script component to fail at any cost, just replace the key with 1 and proceed.

Below is the change I made to the code.

if (ranges == null)

{

Row.ProductKey = 1;

//commented

//throw new NullReferenceException("Couldn't find //value for product number " + productNumber);

}

Am I missing something here or should I do anything else.. Please have a look at it. It is very critical for me.