?
Solved

SSIS - Data Flow - two lookups to the same table

Posted on 2010-09-16
6
Medium Priority
?
823 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
[X]
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
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 1000 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

719 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