Case Sensitivity in SSIS Lookups

September 15, 2009

Sooner or later you will come across an instance where lookups in SSIS 2005/2008 fail due to attempted matches on values in which do not have exactly the same case. This is because the lookup transform itself is case-sensitive.

A possible solution is to use partial caching which will force the comparison to be made at the database level (assuming your collation is case insensitive). This option will also take a performance hit in most instances.

The best plan is to use the UPPER function in your T-SQL (or a character map transform) for your incoming values. Suggest that the lookup query itself forces uppercase so that you are guaranteed no misses. Jamie Thompson covers this in further detail on his old blog

Using MDX queries in SSIS without OpenQuery

September 6, 2009

Recently i needed to use data from an SSAS2005 cube as a source within an SSIS data flow. I thought this was going to be pretty simple but only managed to do it nicely after a bit of searching around.

Firstly i tried the obvious way i.e. create a new OLE DB connection by right-clicking from the connections pane, and then choosing the “Native OLEDB\Microsoft OLE DB Provider for Analysis Services 9.0” Provider. In the data flow i used an OLEDB source and types in my MDX query. Preview worked fine but executing the package kept giving me an error (80004005). Darren Gosbell covers this briefly here, and also looks at the linked server with OpenQuery method which i have used elsewhere.

While the linked server solution does work it certainly isnt elegant and does require server settings to be changed. This is not always feasible in reality.. or at least not without due process being followed in production environments. Luckily i came across another solution. Instead of using the OLE DB connection, we instead use an ADO.Net connection, and then use “.Net Providers for OleDb\Microsoft OLE DB Provider for Analysis Services 9.0” provider and configure the connection to your cube. From there use a Datareader source and configure it as per normal, substituting your MDX query instead of T-SQL. The slight downside is that all fields are returned as DT_NTEXT. You will need to convert to DT_WSTR first and then perform a second conversion to the required types. Still better than openquery!