May 8, 2008 at 3:07 pm
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#/.net
The 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?
May 8, 2008 at 3:20 pm
From what you're describing - I'd actually steer clear of the script component, and look into the conditional split task instead. In other words - split your data on whether there is something in the lookup table or not, and have two branches - one handling the update, one handling an insert.
By the way - here's an article which is a very decent step-by-step as to how to tackle something like you're describing:
Anatomy of any Incremental Load[/url]
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 8, 2008 at 3:59 pm
Hi Matt,
Im checking the Article (very interesting by the way), but i'm noticing that i will make an left join to see if the data exists or not.. but of the entire table. If i have a table of 100 million rows the process will collapse. Thats what im trying to avoid doing, reading and entire table instead of specific data (with where clauses).
What do u think there?
May 9, 2008 at 4:01 pm
Hey Matt,
I just came back thank you for the link.
It's the best articule i've read so far about inserting/updating rows.
I still have to test the response on big tables.
Thx again.
May 9, 2008 at 6:12 pm
It IS a scary thing with that many rows - but SSIS seems to manage pretty well anyway.
Glad it helped. Let us know if you need something else!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy