0

I'm working on a project where i need to do lookups on a data warehouse server in Integration Services. My problem is that I need to be able to change what database it i performs the lookup to. The databases are design wise identical.

I have solved this problem with a script component before, where for each row, if the database id have changed, the connection changes, example below 

try {
  if (databaseNr != Row.DatabaseNr) {
    try {
      databaseNr = Row.DatabaseNr;
      currentCatalog = "db" + Row.DatabasNr;
      connection.ChangeDatabase(currentCatalog);
    } catch (Exception e) {
      ComponentMetaData.FireWarning(0, ComponentMetaData.Name, e.Message, "", 0);
    }
  }
string command = "SELECT Id, Name, Surname FROM [" + currentCatalog + "].[TableName] WHERE Id = '" + Row.OrderID + "'";

But it would save me a lot of trouble if this was possible with the lookup component.

So my question is: Is it possible in any way to use column data to change what database to perform a Lookup with the Lookup component?

Grateful for any help!

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
August
  • 3
  • 1
  • Are the databases on same server ? In that case you can consider creating the lookup query dynamically outside the task flow. – Ako Mar 09 '15 at 12:53
  • Yes the databases are on the same server. I'm not really sure how that would be possible, could you explain further? – August Mar 09 '15 at 13:08
  • 1
    Help me understand: your source query has some identifier to indicate what catalog the data resides in. These catalogs have consistent entities in them (likely you've physically segmented clients by catalog to prevent comingling of data) and you need to run some query across them all (perhaps a billing report). Is that about accurate? As Ako asked, is it possible that Client20 suddenly accumulates so much data you need to push them to their own server? – billinkc Mar 09 '15 at 15:02
  • Help me understand: your source query has some identifier to indicate what catalog the data resides in. These catalogs have consistent entities in them and you need to run some query across them all (perhaps a billing report). Is that about accurate? That sounds accurate. As Ako asked, is it possible that Client20 suddenly accumulates so much data you need to push them to their own server? I am not sure to be honest, It's a pretty simple lookup (at least with script component) and the results are saved as flatfiles if that info is of any use – August Mar 09 '15 at 15:17

1 Answers1

1

What you can do is:

  • Goto control flow
  • Select your data flow task
  • Goto properties and select the lookup component
  • Create an expression for the lookup, you can reuse a query prepared in a script task.

enter image description here

Ako
  • 1,193
  • 14
  • 22
  • I would need to change the value for every row in the data flow because the data comes in flatfiles with mixed "data base ids". I guess there is no expression to get a row column value for each row handled. Maybe a script component is the smoothest way to do this.. – August Mar 09 '15 at 15:07
  • It think that it will be very inefficient to change it per row. Default behavior is to preload data for the lookup before the dataflow starts. You can turn it off. But consider preloading data from all databases (I do not know the size) and adding a column with databasename and match on it. – Ako Mar 09 '15 at 15:25
  • I think it works quite okay, I just want a better way because i need to write this lookup script in a few places. and i only change database when the ID changes. Preloading could work if it was smaller but it's a kind of huge database with about 30 (identical) databases combined. Thanks though :) – August Mar 09 '15 at 15:41