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
  • 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?
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud


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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS Deployment problem 5 67
Database Integrity 1 50
MS SQL Server select from Sub Table 14 26
SQL Group By Question 4 20
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 setup several different housekeeping processes for a SQL Server.

830 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