tricky data import. need strategy help.

[JonSchmidt]

I have to import data (on a weekly basis) from one DB to another. The databases are structered differently. There source DB has about 10 tables and the destination 4.


The source DB is structured to be very static with tables and columns specific to certain fields, where as the the destination is designed to be very flexible. The destination DB has a Products table with one-to-one product attributes, and for the one-to-many it has a Category table, a CategoryValues Table containing all possible values for those Categories, and a linking table to link together the Products with the Category Values. For instance, to store the "Special Services" associated with a product, the source DB would have a Special Services Table with a field for the ProductID and for the Special Service offered, whereas the destination would have an entry for "Special Services" in the Cateogry Table, all possible "Special Services" offered stored in the CategoryValues table, and a table that would link the ProductID with each CategoryValueID representing which "Special Services" belong to that product. Sorry if Im overexplaining the design, but clearly I have to do alot of transforming in this import, which Im not clear on how to handle.

Also, I need to import conditionally. If the Product/Version already exist in the destination table, I shouldn't import it. Neither of those fields are Primary Keys either. So I have to do some sort of a lookup and insert based on that result.

So I played with DTS Packaging for a while yesterday, and I think there might be an answer there, but I can't figure out how to handle the conditional inserting (I haven't gotten into the transforming yet). Can I do it with a Data Driven Query or maybe a complex ActiveX Script? Can you do inserts from inside an ActiveX script? Any and all ideas welcome. Thanks

Jon

 

[MDXer]

bring the data into staging tables which have the same structure as the source table, then use stored procedures to conditionally move the data to the destination tables. Trying to do this all within DTS is going to very complex and possibly more work than it is worth.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[JonSchmidt]

I forgot to mention that I need to automate this so that it runs once a week. Thats why I was doing it in DTS. Still possible to do with your method?

It seems like I can do some inserts using vbscript from my ActiveX script that would take care of it, though Im still not sure how efficient that is though.

 

[JonSchmidt]

Actually, I suppose I could import the data to my staging tables in DTS, then call my SP also from my DTS package. Would that be any more efficient than writing a complex ActiveX script?

 

[MDXer]

would probably be more efficient as active x has a nice little overhead.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck