I want to create a job with a specific query to export some date from SQL Server to Oracle. How can I schedule this? Thank you!
I want to create a job with a specific query to export some date from SQL Server to Oracle. How can I schedule this? Thank you!
How you move forward with implementing this will depend on a variety of factors, but the following steps outline this process at a high level. Of course, you'll want to choose the configurations that best suite your specific environment and business needs.
Create an SSIS package with an OLE DB Connection Manager to SQL Server
Add a Connection Manager for Oracle. Some options are the Microsoft ODBC driver for Oracle or OLE DB Provider for Oracle. You can here more about setting up connections here.
After the connections are configured, you'll want to create components to export the data, likely using a Data Flow Task (or multiple). How this is setup will vary greatly depending on the business needs. You can use the query mentioned in your question for the SQL command in the OLE DB source to SQL Server. I'd also recommend using stored procedures as opposed to static queries.
After the package is complete, you can add it as a job in SQL Agent and schedule it to run at the desired frequency. Make sure that the account the job runs under has the necessary permissions, including on both the SQL Server and Oracle objects involved.
This is not required, but installing the SSIS catalog, deploying the projects there, and running them from this instead of the file system will offer many benefits. This link contains more information on SSISDB (the catalog).