SSIS - Data Flow - two lookups to the same table

Posted on 2010-09-16
Last Modified: 2013-11-10
In my data flow, I need to run two different lookups to the same table and pull out different values. What I'm getting is that when I build the second lookup, it assumes I'm doing the same type of lookup and automatically uses the link built from the prior lookup and does not allow me to build new relationships in the 2nd lookup.

How can I build the lookup so that I can reference the same table but under different relationships?
Question by:wppiexperts
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
LVL 30

Expert Comment

by:Reza Rad
ID: 33696548
I can't understand what you mean exactly?
what do you mean by under different relationships?
could you explain more in details?

Expert Comment

ID: 33699532
write a sql statement to get your necessary columns instead of selecting the table from the dropdownlist. If needed, use aliases.

Author Comment

ID: 33701040
For example...I have a time table that stores universal dates and times that can be referenced by a keycode. As I'm loading in my data, say for instancee, I have a field that stores orderdate (mm/dd/yyyy) and a shipdate which is actually comprised of 3 fields (one for month, one for day, one for year).

These fields can store different date values but in the process of loading the data, both need to pull a timecodekey from the time table.

So in the data load flow, I have one step that does a lookup to the time table for the orderdate (using a datetime field) and in the process builds the link between my load data and the time table. In another lookup step, when I try to do a join between my data and the time table, it automatically assumes I want to use the relationship built in the prior step, but actually, I want to build a new relationship using the month, day and year fields now.

How do I override what SSIS wants to use as the default relationship and use the new one?
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.


Expert Comment

ID: 33701408
How did you configure your lookups? Did you use the dropdown, or did you type in a SQL Statement?

Does the package have the same behaviour when you use SQL statements?

Author Comment

ID: 33701584
I've only been using the dropdown option. Not sure how to buld the query using data loaded in the prior script transformation. If you could give me an sql example of how to reference a field like that, I can give that a shot.
LVL 30

Accepted Solution

Reza Rad earned 250 total points
ID: 33702604
you can use merge join instead of lookup for this case,
and after merge join use conditional split transformation to find out match and no-match resutls.

could you provide sample of input data stream, and also sample of lookup tables, and requested results for sample data and I will explain solution for you in details.

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question