Hi friends, I know this question must have been asked by many who are beginners to SSIS like me about migrating data from oracle database to sql server database but i still would like to raise this query again. I have table called tbl_employee_oracle in oracle database from where i need to retrive the latest records(eg. latest record of each employee) from the table and populate the result into the sql server database table which is tbl_employee_sqlserver using ssis. The logic i thought of doing this is as below; 1. Retrive all employees records from oracle database table. 2. Dump these records into a temporary table(will have same table structure as in oracle database) in sql server. 3. Creating a stored procedure in sql server which will contain logic to select the most recent records from this temporary table. 4. Populate this result set into the final table of sql server. Please suggest me if working on this logic is worth and any alternate logic to accomplish this task.

There are many opitons, but choose among for your scenario 1. If your employee table is small with less than a few hundreds of record, you want to choose SCD Type 1 if not preserving histoy, incase of preserving history of records choose SCD2, 2. If you have less than few millions of records you can choose look up to direct unmatched records to insert into Sql Server Destination and direct matched records could be updated using OLEDB Destination or Try Merge Left outer Join transform. 3. If you have several millions of records, you may want to use your above method. 4. Configuring to use CDC could be last option where there are large volumes of updates and inserts every few min to hours. But I'm not sure if this option can be set in Oracle Source.Please mark this post as answer if it resolves your Problem. You may even mark it as helpful. Thanks

hi, i am doing data migration for a table from oracle to mssql using ssis and the table structure in both the database is same. I have developed the package and the migration was successful. The data flow for migration from oracle to mssql is as below. 1. OLEDB SOURCE -Oracle 2. DATA CONVERSION - Converting fields from oracle having varchar as type to nvarchar for mssql 3. OLEDB DESTINATION - mssql There are approximately 2 lakh records in the oracle db table and it took around 60 mins to migrate data and i want to migrate data of 12 tables so i am little anxious how much time it is going to take. All the 12 tables data migration needs to be developed in the same ssis package. Please suggests.

i used fastload access mode option of oledb destination and data was migrated in 60 secs, this seems to have been resolved the issue.