SSIS Lookup multiple identical databases

Question

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!

Answer 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.