Saturday, February 25, 2012

data warehouse truncate question

have an SSIS project and I have a package that drops and creates the
database, creates tables, then I have a package that loads the data into the
data warehouse. Now, I got the request in to only load the data and not drop
and create the database every time the package runs due to the package is
going to run every 5-10 minutes. Now I never created an SSIS package until
now, so how can I accomplish in only loading the tables every time its ran
instead of dropping and creating the database every time the package is ran?
so in a nutshell.
I need a package to load the data only if the data does not exsist and not
drop and create the database every time.
any suggestions on how this can be done?Two methods come to mind.
1) Simple insert where not exists statements for each table. Can be VERY
inefficient, but easy to construct.
2) Build system that tracks the last-inserted key value for each table.
Reference the appropriate key in the where clause of each insert statement.
If you need to also track modified data, then it gets MUCH more complicated
because you need triggers on every base table to insert the modified row's
PKs into a refresh holding table which would also be reference for the
warehouse update statements.
TheSQLGuru
President
Indicium Resources, Inc.
"Mike" <igotyourdotnet@.gmail.com> wrote in message
news:%23%23HSmywaHHA.1300@.TK2MSFTNGP02.phx.gbl...
> have an SSIS project and I have a package that drops and creates the
> database, creates tables, then I have a package that loads the data into
> the
> data warehouse. Now, I got the request in to only load the data and not
> drop
> and create the database every time the package runs due to the package is
> going to run every 5-10 minutes. Now I never created an SSIS package until
> now, so how can I accomplish in only loading the tables every time its ran
> instead of dropping and creating the database every time the package is
> ran?
> so in a nutshell.
> I need a package to load the data only if the data does not exsist and not
> drop and create the database every time.
> any suggestions on how this can be done?
>
>

No comments:

Post a Comment