My company has a Data Warehouse offsite in another state. That database is Oracle, I need to ETL data back to my local server to create SSRS reports. What is the best strategy?
I currently have a database link and I am running OpenQuery to pull over datasets. I am concerned this is not the most efficient way to got about this, but I needed to have something up and running on a scheduled job so I am running the open query in a procedure and a job in SQL Server Agent.
I am no ETL expert, but I am interested in best practices. I do have SSIS but have not given it a shot yet.