Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
loudbliss
Starting Member
37 Posts |
 Posted - 2008-05-08 : 17:06:50
|
| Hi, this may be a basic question but i can't get my mind straight and need some advice.I've read there's limitations on using the Lookup Task.What i want to do is simply look if a Data exists on a table, if it does i want to update some columns (or delete the row and insert a new row), if it doesnt exists i want to inser a row.That's all, easy as hell in sql and c#/.netThe thing is i dont know whats the best way of doing it in SSIS. My limited ssis knowledge tells me Script Component should be able to do the work. That is, send it a variable holding the values i want to verify in the database and depending if it exists to the whats necesary.So my question is, can i connect to the DB via Script and make my custom selects there?If so how can i do it?If not, what's the best way of doing that simple task? |
|
|
tmitch
Yak Posting Veteran
60 Posts |
Posted - 2008-05-08 : 20:49:31
|
| Actually, you can do this with the help of a Lookup Transformation. Send your data to the Lookup Transformation object, and map the input field to the lookup field on which you need to match. You'll need to change the options under Configure Error Output, specifying to Redirect Rows (note that this is NOT the default setting) on error. The net result is that rows that are not matched to the lookup table will be sent to the Error output, indicated by the red connector. For your matched rows - the green connector - add a SQL Component to do your update or delete. For the unmatched (error) rows, attach this output to your destination table to insert the records.Post back here if this is unclear, and I'll try to help further.Tim---------------------Tim Mitchellwww.BucketOfBits.com |
 |
|
|
loudbliss
Starting Member
37 Posts |
Posted - 2008-05-09 : 08:39:38
|
| Hi Team, thx for the response.I've heard about using the Lookup Transformation before, but what if the lookup table im using has 20 million rows? Won't the package collapse? (that's my real problem since i can't find a efficient way to lookup data in a table with million rows) |
|
|
|
loudbliss
Starting Member
37 Posts |
Posted - 2008-05-09 : 18:03:16
|
| We'll im back with the link that answered all my questions, just in case somebody is interested:http://www.sqlservercentral.com/Forums/Topic447281-208-1.aspx |
|
|
|
l-jeff@excite.com
Starting Member
39 Posts |
Posted - 2008-05-14 : 16:10:55
|
| Hey loudbliss,How were you able to get the "if a Data exists on a table, if it does i want to update some columns (or delete the row and insert a new row), if it doesnt exists i want to insert a row" to work in SQL? I just got this project yesterday and was able to get the info out of database but I can't get it to update\insert only new info. Help would be appreciated!Lisa Jefferson |
|
|
|
loudbliss
Starting Member
37 Posts |
Posted - 2008-05-27 : 09:16:11
|
| Hey Lisa,Sorry for the delay.I basically followed the instructions at http://www.sqlservercentral.com/Forums/Topic447281-208-1.aspx.After the Conditional Splits Determines a row exists it takes you to a ChangedRows destinations which will be mapped to a new Table that you will create there in the destination probably named something like YourTableChangedRows, what that will do is insert the updated rows to that table.After that in your Control Flow you are going to add a Set Based Update that basically updates YourTable values with those of YourTableChangedRows. You simply have to write the update statement there.The article explains is but i know is kind of tricky to understand if youve never done it before as i experienced.Any question please write back, i will be checking the post regularly.Manuel |
|
|
|
|
|
|
|
|
Lookup Data in DB