locked
setup adjust the uploading data from Linked Server Oracle and load data into database MS SQL in a set time RRS feed

  • Question

  • Hello.
    Perhaps, the question isn't really suitable for this forum, but I can't Google it.
    I have 2 database.

    1. Database MS SQL 2008 and Server 2008.

    2. Linked Server Oracle DB.

    I want to adjust the uploading data from Linked Server Oracle and load data into database MS SQL in a set time (1 per every 15 minutes, 1 per every 24 hours).
    What tools, methods can I use for this? Thanks.
    Sunday, January 15, 2017 2:13 PM

Answers

  • You can create a linked server to the oracle database and then run a query against the linked server to import the data to SQL server. Then schedule this query to run as a job.

    Another option is to use SSIS to do the transfer and schedule it as a job.

    A third option is to right click on the database, select tasks and import data. Click Next, select Microsoft OLE DB provider for Oracle, Click the properties button, enter the oracle sever name, user name and password and check Allow for Password saving. Then click OK and Next. Select the tables or views you with to copy. Click next. The next dialogs will allow you to import data to the SQL Server. Select your SQL server, database, and account. Follow the defaults and the package will run. You will have an opportunity to save your package. You can give it an name and then schedule it later.

    • Proposed as answer by Ekrem Önsoy Sunday, January 15, 2017 6:07 PM
    • Marked as answer by Aleks Roth Wednesday, January 18, 2017 11:21 PM
    Sunday, January 15, 2017 4:11 PM

All replies

  • You can create a linked server to the oracle database and then run a query against the linked server to import the data to SQL server. Then schedule this query to run as a job.

    Another option is to use SSIS to do the transfer and schedule it as a job.

    A third option is to right click on the database, select tasks and import data. Click Next, select Microsoft OLE DB provider for Oracle, Click the properties button, enter the oracle sever name, user name and password and check Allow for Password saving. Then click OK and Next. Select the tables or views you with to copy. Click next. The next dialogs will allow you to import data to the SQL Server. Select your SQL server, database, and account. Follow the defaults and the package will run. You will have an opportunity to save your package. You can give it an name and then schedule it later.

    • Proposed as answer by Ekrem Önsoy Sunday, January 15, 2017 6:07 PM
    • Marked as answer by Aleks Roth Wednesday, January 18, 2017 11:21 PM
    Sunday, January 15, 2017 4:11 PM
  • setup linked server and schedule a agent job as per your timing.
    Wednesday, January 18, 2017 10:04 AM