Solved

SSIS - Data Flow - two lookups to the same table

Posted on 2010-09-16
6
811 Views
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?
0
Comment
Question by:wppiexperts
  • 2
  • 2
  • 2
6 Comments
 
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?
0
 
LVL 1

Expert Comment

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

Author Comment

by:wppiexperts
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?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Expert Comment

by:da-zero
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?
0
 

Author Comment

by:wppiexperts
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.
0
 
LVL 30

Accepted Solution

by:
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.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now