Solved

SSIS - Data Flow - two lookups to the same table

Posted on 2010-09-16
6
812 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
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

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

773 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