I awoke this morning to find my inbox full of automated replies from msftconn@microsoft.com thus indicating that someone on the SSIS team has been busy updating many of my submissions to Connect. Generally it was the usual "Closed, Won't Fix" update (grrr) but there was some good news about the following four submissions:
Title: Case insensitive LOOKUPs
Description: LOOKUP component always does case sensitive comparisons. If you are using a case-insensitive RDBMS (which by default, SQL Server is) then you can get incorrect results and if you are using the component to determine new rows then you can get PK violations (which I have just got)
Proposed Solution: Have an option on LOOKUP component for case insensitive comparisons
Status: Resolved (Fixed)
Title: LOOKUP component must be populated from more than just a SQL statement
Description: Currently have a situation where we have to do a lookup. Unfortunately the data is sourced from a web service rather than a DB table.
The LOOKUP component cannot be used because that can only do lookups from a relational source. This is one of many MANY shortcomings in the LOOKUP component.
Proposed Solution: Loads of potential ways. Most obvious is to allow lookups from a web service, or perhaps from an XML file. Another way around this (and one which I like) is to populate the LOOKUP from a recordset stored in an object variable. This really does open up the functionality because the recordset can basically be populated from anywhere using the recordset destination component.
Status: Resolved (Fixed)
Title: Lookup cache is unnecassarily repopulated in a loop
Description: If you have a LOOKUP inside a data-flow task inside a For/ForEach Loop then the cache for that LOOKUP will be getting populated on each loop iteration with (almost) exactly the same data. This seems pointless. Why redo work? Can't we keep the cache populated inbetween iterations?
Proposed Solution: Keep the cache populated between iterations. There would be problems if each loop iteration causes the cache data to be changed but you need to find a way around this. Dynamic Lookup Caches a la Informatica would solve the problem.
Status: Resolved (Fixed)
Title: Enhancements to LOOKUP
Description: A few problems with LOOKUP component:
1) Cannot set SQL statement that populates the LOOKUP cache dynamically. This means you cannot dynamically filter teh data in the cache - it would be a good idea to be able to do this. More info here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=147457&SiteID=1
2) There is no AlwaysUseDefaultCodePage roperty which there is if you use OLE DB Source. This is a problem if you're going against Oracle because a code page warning is automatically generated
Proposed Solution: 1) Put a property expression on SQLCommand property
2) Add a AlwaysUseDefautlCodePage property
Discussed at length here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=147457&SiteID=1
Status: Resolved (Fixed)
There's a clear pattern here. There are big improvements to the way we do lookups from within a dataflow task in SSIS2008. Given that these submissions have now been closed I'm hopeful they'll be in the next CTP.
-Jamie