I maintain BI that needs to import the data from Oracle every day. I use the import wizard to create package using .Net provider for Oracle. I've over 1400 tables, so I just choose all of them and let the wizard create the package for me, which I'll put it inside the job later on. It takes over 2 hours to create the package. Now the problem Oracle team do the changes in their database all the time, which lead to failure of my package. It happens almost every week or two, then I've to create the package all over again. It's happening almost 2 years. I've tried to ask them notify me their change logs several times, but no luck.
The logic of my import is I do drop all the tables every day, and package creates tables and imports the data.
Is there any solution that will work in this case that I'll do it once and works forever. All I want is latest data with structure from Oracle to SQL Server. I don't know any people from Oracle team, all i have is their database name and one user id to access the Database.