<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2005
»
SQL Server 2005 Integration Services
»
SSIS LOOKUP QUESTION
13 posts, Page 1 of 2
1
2
»»
SSIS LOOKUP QUESTION
Rate Topic
Display Mode
Topic Options
Author
Message
SSIS.COM
SSIS.COM
Posted Thursday, August 19, 2010 3:44 PM
Valued Member
Group: General Forum Members
Last Login: Friday, October 05, 2012 1:24 AM
Points: 63,
Visits: 261
Wanted to use lookup transformation for the following:
- Pull records from oledb source
- Lookup in oledb destination first to see if record exists
- if record does not exist, send to destination as insert.
The result just dumps everything into the destination when the Destination is Empty(In my case it is a blank table in the starting)
I have tried no cache option also but thats not working too...as there are duplicates...
I don't want any duplicates in my loookup column....
PLEASE HELP...THIS IS KILLING ME
Post #972210
SSIS.COM
SSIS.COM
Posted Thursday, August 19, 2010 6:32 PM
Valued Member
Group: General Forum Members
Last Login: Friday, October 05, 2012 1:24 AM
Points: 63,
Visits: 261
The only solution I have found till now is this
I am sending this data to a temp table instead of sending it to a Destination table and then I have created one more data flow task for doing SELECT DISTINCT FROM TEMP TABLE.
Let me know if there is a better way to do this.
Post #972248
Koen Verbeeck
Koen Verbeeck
Posted Friday, August 20, 2010 12:34 AM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 6:30 AM
Points: 9,410,
Visits: 6,495
Where exactly are there duplicates?
In your source or at the destination?
If there are duplicates at the source, performing a lookup while you are writing to the table destination will not work perfectly, as you are inside a transaction.
I think the best method is making sure you write a good SQL SELECT statement to get the data in the OLE DB Destination so that there are no duplicates to begin with.
How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
Member of
LinkedIn
. My blog at
LessThanDot
.
MCSA SQL Server 2012 - MCSE Business Intelligence
Post #972305
Learner1
Learner1
Posted Friday, August 20, 2010 9:43 AM
Old Hand
Group: General Forum Members
Last Login: Thursday, May 23, 2013 9:02 PM
Points: 323,
Visits: 804
This is simple..
You have to use a SORT transformation and de-duplicate on LOOKUP columns.
You can remove duplicates either before using lookup Tx or after that before inserting into your destination.
---------------------------------------------------
Thanks
Post #972638
SSIS.COM
SSIS.COM
Posted Friday, August 20, 2010 11:10 AM
Valued Member
Group: General Forum Members
Last Login: Friday, October 05, 2012 1:24 AM
Points: 63,
Visits: 261
I am sorry ssis learner..But, I didn't understand how Ican achieve this by sort transformation
Post #972705
Koen Verbeeck
Koen Verbeeck
Posted Friday, August 20, 2010 11:13 AM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 6:30 AM
Points: 9,410,
Visits: 6,495
da-zero (8/20/2010)
Where exactly are there duplicates?
In your source or at the destination?
If there are duplicates at the source, performing a lookup while you are writing to the table destination will not work perfectly, as you are inside a transaction.
I think the best method is making sure you write a good SQL SELECT statement to get the data in the OLE DB Destination so that there are no duplicates to begin with.
That is actually one of the worst things you can do (it works though), as the SORT component is a blocking component. If your source contains 1 million rows, you'll have to load them all in memory and then sort them. There are much better alternatives that don't hit performance so hard.
How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
Member of
LinkedIn
. My blog at
LessThanDot
.
MCSA SQL Server 2012 - MCSE Business Intelligence
Post #972706
Koen Verbeeck
Koen Verbeeck
Posted Friday, August 20, 2010 11:15 AM
SSCrazy Eights
Group: General Forum Members
Last Login: 2 days ago @ 6:30 AM
Points: 9,410,
Visits: 6,495
SSIS.COM (8/20/2010)
I am sorry ssis learner..But, I didn't understand how Ican achieve this by sort transformation
There is an option at the SORT transformation to remove duplicates. But see my previous post to see why you should avoid this transformation.
How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
Member of
LinkedIn
. My blog at
LessThanDot
.
MCSA SQL Server 2012 - MCSE Business Intelligence
Post #972708
Elliott Whitlow
Elliott Whitlow
Posted Tuesday, August 24, 2010 12:58 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 5,870,
Visits: 4,893
I have done this before with SSIS 2005, it is easier with SSIS 2008..
In the Lookup comopnent for 2005 you have 2 outputs, found and error. In 2005 I have generally used the error to indicate NOT found and use that for inserts, where I use the found as updates. You can get it to do this by setting the error as redirect.
Is that clear?
In SSIS 2008 there is three outputs, found, not found, and error.. much better.
CEWII
Post #974379
SSIS.COM
SSIS.COM
Posted Tuesday, August 24, 2010 2:57 PM
Valued Member
Group: General Forum Members
Last Login: Friday, October 05, 2012 1:24 AM
Points: 63,
Visits: 261
Thanks to Elliott and everyone who reponded.
Here is a brief description of my problem
Initially my destination(reference) table is empty... I am trying to load data from my staging table into a Customer Dimension table(dimension table so, each customer will have only one record with their customer no, name etc )...
Here is my sample input data from staging
custName CustNumber Custemail CustLocation CustLocNumber
A 1 XX H 1
A 1 XX D 2
B 2 YY M 3
B 2 YY C 4
C 3 ZZ B 5
And Here is my customer Dimension Table(My destination&Refernce Table)(and this is empty)
CustName CustNumber Custemail
So, when I look up CustNumber in both these table since there is no data in the reference table everything goes to the NO MATCH OUTPUT. Since these are new records I want to insert these records into the Destination(Ref) table. But, when I do this all my records are getting inserted into the Dim table like
custName CustNumber Custemail
A 1 XX
A 1 XX
B 2 YY
B 2 YY
C 3 ZZ
IS there a way I can get a result in Dimension table like
custName CustNumber Custemail
A 1 XX
B 2 YY
C 3 ZZ
Let me know if I am confusing you again.
Post #974470
Elliott Whitlow
Elliott Whitlow
Posted Tuesday, August 24, 2010 3:51 PM
SSCertifiable
Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 5,870,
Visits: 4,893
I'd probably change my select query from the staging and do a distinct and only include the fields I needed. This should weed out dupes and make the database engine to the work for you without making SSIS do it.
CEWII
Post #974506
« Prev Topic
|
Next Topic »
13 posts, Page 1 of 2
1
2
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
