Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I optimize Upsert (Update and Insert) operation within SSIS package?

Tags:

I am not a DBA but I do work for a small company as the IT person. I have to replicate a database from staging to production. I have created an SSIS package to do this but it takes hours to run. This isn't a large data warehouse type of project, either, it's a pretty straightforward Upsert. I'm assuming that I am the weak link in how I designed it.

Here's my procedure:

  1. Truncate staging tables (EXECUTE SQL TASK)
  2. Pull data from a development table into staging (Data Flow Task)
  3. Run a data flow task
    1. OLE DB Source
    2. Conditional Split Transformation (Condition used: [!]ISNULL(is_new_flag))
    3. If new insert, if existing update

The data flow task is mimicked a few times to change tables/values but the flow is the same. I've read several things about OLE DB components being slow to updates being slow and have tried a few things but haven't gotten it to run very quickly.

I'm not sure what other details to give, but I can give anything that's asked for.