October 24, 2006 at 1:48 pm
1) We have OLTP DB on 2000 & OLAP DB on 2005.
2) We have decided to use SSIS for migration and interface is BIDS to develop, build, deploy & install SSIS packages.
3) Initial full migration is simple with column mapping and queries.
4) Incremental nightly scheduled migration is based on datetime value of source table column versus datetime value of end of most recent migration.
5) Inserts, updates and deletes are allowed in source. Whatever is updated and deleted in source I am finding the PK's via query compare via linked server in stored proc & deleting all these records from target all this in stored proc.
6) Now I have to pass these PK's along with inserted PK's to select statement of SSIS package against source to insert into target.
7) We even have to encrypt some tables colmns data while reading from source so that it is secure in network and gets into target database as such requiring decryption for reports and front end display.
8) Is my approach okay? How do I pass the PK's to SSIS package SQL statement. How, where and when do I fit in encryption part.
9) Are there any better ways that you can think of.
October 24, 2006 at 8:10 pm
A few thoughts.
2. Yes, a good idea to use SSIS.
3. Historical load was simple - usually the easiest part of the load.
4. Incremental load based on business date timestamp - a common approach.
5. Not sure how intensive the querying will be, but there are alternatives to determining 'changed' data, including the use of triggers and a separate table to store all changed data.
6. Not sure what you mean by this?
7. You will need to determine if your approach will be able to deal with encrypted data based on requirements.
8. You should be able to do pretty much everything from SSIS, so I would recommend that you be confident enough about this technology before embarking on your project.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply