I know this question could have a real complicated answer, but I need some direction. I am building a data warehouse and bringing data in from another sql database. In the past, I have utilized the following technique:

- DTS
- Step 1 - drop table "ProductLines"
- Step 2 - Import Table ProductLines from original database
- Step 3 - SQL task - query looks for items in ProductLines not in ProductLines2, new records added to ProductLines2

There are a few additional fields that I want to add to the original product lines table, the main reason why I need the second table. now I know there is a better way, and I think it entails using a data driven query. I would like to go right from the original database into the data warehouse directly, only bringing in the new records.

Help would be appreciated

CJL

While this works and is fine for small datasets what happens when you have
500,000 products? or 10 million order lines ?
It doesn't scale well.

What about a linked server?

That way you can filter nicely.

A DDQ is great for loading destinations where you can see from the source
something like a change indicator. This way you know that the row has

1. Changed
2. Been inserted
3. Been deleted.

I usually implement this through triggers on the source table which load a
logging table. I use the logging table as my Source for the load.
--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Lockeness" <anonymous (AT) discussions (DOT) microsoft.com> wrote

sql database. In the past, I have utilized the following technique:
know there is a better way, and I think it entails using a data driven
query. I would like to go right from the original database into the data
warehouse directly, only bringing in the new records.

Then use linked servers. They are easy and do what you want.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"lockeness" <anonymous (AT) discussions (DOT) microsoft.com> wrote

I just want to better automate the collection of key metrics into an
executive focused datawarehouse.