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