May 25, 2010 at 11:07 am
I have a scenario.
I have an Oracle DB as source in whihc Employee table with 1 million records are there.
Also there is another employee details table which stores the extra details about these
1 million employees.
I already Have 10K employee records in the SQL DB (Destination DB) employees table.
The empolyee details table in SQL is empty.
Now the requirement is to develop a package which will transfer the employee details
of these 10K employees to the SQL server DB(Employee details table)
& the rest of the employees (1 million - 10K) employees to an excel file
Whats the best way to desing this scenario?
May 26, 2010 at 4:23 am
I would do the following:
1. To get the 10k customer details.
Set up a OLE DB Source which will read your SQL Server table with 10k customers. In the dataflow, add a lookup component. In this lookup component, you will link the customer with the customer details on the Oracle Server through the business keys. You keep the details as result of the lookup and you write them to the corresponding SQL Server table.
2. Write customers to Excel. (not sure why you want 1 million customers in an Excel file, mais bon)
Set up 2 OLE DB Sources, one for SQL Server and one for Oracle and read the customer data. Then use a UNION ALL to merge the two inputs to one stream. Then write it to an Excel file with an Excel Destination. Make sure you use Excel 2007 or above, or it will fail.
(and make sure the package runs in 32 bit, Excel and 64 bit don't work together very nicely)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply