December 23, 2007 at 10:32 am
Does anyone know how to use SSIS in sql 2005 to migrate data from an oracle database?
December 23, 2007 at 2:19 pm
You can use the SQL Server Import Export wizard to create a SSIS package as follows then build upon it using the 'SQL Server Business Intelligence Development Studio'
1. Open SQL Server Management Studio
2. Right click on a database then click 'Tasks'then click 'Import Data'
3. 'SQL Server Import and Export Wizard' opens. Click on 'Next'.
4. In the 'Data Source' list select 'Microsoft OLEDB provider for Oracle'. Click the 'Properties' box and enter the Oracle server name, user name and password and click on 'Test Connection' to test the connection to the Oracle database you want to migrate the data from.
5. Following the remaining steps of defining the source and target schema objects.
6. Select the option to 'Save SSIS Package' to 'File System'
before concluding the wizard.
7. Now open an instance of 'SQL Server Business Intelligence Development Studio'. Create a new 'Integration Services Project'.
8. Open the 'Solution Explorer' in the and add the dtsx file that was created in the step 6 to the 'SSIS Packages' node.
Double click on the newly added package to the project (.dtsx). It will open the design time view of the SSIS packege that you created through the wizard. Ypo can modify/add funtionality to it using the abundant features of SSIS. You can run the package by pressing F5.
You can use the above steps as a jump start. You should directly use the 'SQL Server Business Intelligence Development Studio' as you gain strength to creats SSIS packages from the scratch.
Happy data migration and providing the optimum benefits of the SQL Server technology to the community 🙂
Regards,
Maz
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
December 24, 2007 at 12:26 am
couldint find Microsoft OLEDB provider for Oracle, nearest to that option was the oracle provider for OLE DB, is that what you mean?
December 24, 2007 at 5:12 am
You can use any of the two providers to connect to the Oracle database.
-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply