April 6, 2019 at 9:04 pm
I have a project I'm working on where I'm trying to use SSIS to do frequent replication of data from several SQL Server tables into Oracle tables.
The problem I'm having is multi-fold. I have created staging tables in Oracle as to do a full population of these Oracle tables from SQL Server each time the SSIS pkg is run. One table in SQL Server has approx 325,000 records in it & to get all those records into the Oracle staging table, it's taking 35-40 min... extremely slow. I tried to use a lookup transformation for purposes of identifying new and updated records to just insert directly from SQL Server to the Oracle table but I found out quickly that while the inserts of new records works o.k., the Lookup Transformation doesn't work with Oracle as far as a parameterized lookup is concerned. That's why I just went with the option of importing ALL recs from SQL Server to Oracle staging tables... this still is very slow though for the 325,000 records but the remaining "Merge" statement following afterwards is pretty quick since it's dealing with data going now exclusively from the Oracle staging table to the final Oracle table.
I have read that SSIS deals much better with data going FROM Oracle TO SQL Server. So...
My thought now is to instead create a local temp table in SQL Server, import the data from Oracle into the SQL Server temp table, do either a Lookup Transformation or a straight SQL statement between the SQL Server table & the temp table in SQL Server (that contains the just-imported data from Oracle) for purposes of populating ANOTHER "final" temp table in SQL Server that only contains brand new records and updated records, which will only be a small portion of the full 325,000-ish records, then take these records from this "final" SQL Server table, upload them into my Oracle staging table and THEN do a much smaller "Merge" stmt into my final/main Oracle table.
I know this seems like a ton of extra steps, but we have tried numerous times to get the Attunity drivers installed on the machine where I'm doing the SSIS development on and even though the Attunity Data Connector shows as installed in Control Panel, I've never been able to get the respective Source/Destination icons for Attunity to appear in the SSIS Toolbox, so we are dead in the water with that preferred option. I know the whole Oracle / SQL Server exchange of data is much more efficient if we could get that data connector working but we seemingly can't. I've seen a million posts reporting the same with difficulty getting the Attunity Oracle Connector functioning.
Any thoughts / suggestions are greatly appreciated.
April 6, 2019 at 10:50 pm
your idea of copying to SQL will work and in some cases could be the best option.
Is the table in Oracle updated outside this SSIS process? If not then you would not even need to copy the table from Oracle down to SQL - just make SQL the master copy.
As for Attunity. What version of Visual studio do you use, what is your target SQL server edition (and is it Enterprise or Standard) and which versions of Attunity did you try to install?
The version required is strictly linked to the version of SSDT installed and not to the version of SQL you wish to target.
I may be able to help on this last bit but need the details. I never had issues adding it to the SSIS toolbox - but there are a few steps to follow, and in some cases a trick to do (if using a 64 bit machine).
April 7, 2019 at 12:34 am
Thank you for engaging this post and for your input. I will try to answer your couple of additional questions.
The Oracle table used to be updated from the master data in SQL Server over to Oracle using I believe DTS. Our company has recently upgraded to a newer version of SQL Server, SQL Server 2012, as I believe we were previously running SQL Server 2008, and I'm told the old DTS functionality is no longer available and we needed to handle the ongoing SQL Server to Oracle replication by writing new SQL Server Integration Services packages to accomplish this task going forward.
I know only bits and pieces of the entire process. I really only know my requested task has been just to replicate several tables out of several SQL Server databases over to equivalently named tables in Oracle. These replication efforts will take place daily usually every 15 or 30 min or once an hour for another database.
My understanding is that the Oracle tables are not updated from any other source except for being replicated with updates & inserts from the master tables which are located in SQL Server.
I am running Visual Studio 2017 on the machine where I'm doing the ssis development.
The target version of SQL Server is SQL Server 2012. I am away from the office right now and honestly cannot remember if we are running Enterprise or Standard but I can check that later on tonight by remoting into my office from my laptop. My family and I are currently in Kentucky for a volleyball tournament my daughter is participating in.
We are running Oracle 12c.
We tried installing Attunity versions three and five. I may be a little confused on that, as we seem to be running a mismatch of SQL server and Oracle versions as the diagrams I have seen show that one version of Attunity is needed for the version of SQL Server we have but that version will not work with the version of Oracle we have.
I am running ssdt version 15.9. From the best I can remember, & I have seen numerous posts online stating if you are running any version of ssdt more updated than 15.8.0, those newer versions will not at all allow for the usage of the Attunity Oracle stay up connector.
Needless to say with all of the issues reported as far as Attunity working or not working with various versions of SQL Server or Oracle or ssdt, it is very confusing as to what version we truly need to have installed.
I hope this information helps, and I can fill in some missing blanks later on tonight once I am back at the hotel and have access to my laptop.
Thank you again very much for your willingness to offer a few suggestions and to offer assistance in helping me try to finish this out & get the appropriate version of Attunity installed and so forth
April 7, 2019 at 7:06 pm
Ranting: Just noticed that you said your company recently upgrated to SQL 2012 - WHY!! at this point in time they should have gone to 2016 or 2017.
A bit of a long post - but should be enough to help you. And some information you may be aware of but posting for benefit of others that read this.
Do confirm your SQL edition - Attunity driver required Enterprise version
As for the Driver compatibility with the Oracle version - I haven't tried it yet but it is possible that V2 works with Oracle 12G even though it doesn't say so. Easy for you to try it out.
As for VS 2017
depending on the version of SSDT you will have the following issues
- some versions do not support the Attunity driver
- - version 15.8.0 and lower support it and version 15.9.0 - those on the middle do not
- some versions will not allow you to create projects targeting SQL 2012 - same as above
The following version of SSDT allows for SQL 2012 and attunity on VS2017
https://go.microsoft.com/fwlink/?linkid=2052454
Release number: 15.9.0
Build Number: 14.0.16186.0
Release date: January 28, 2019
After install and on VS info it shows
Microsoft Visual Studio Professional 2017 Version 15.9.11
SQL Server Data Tools 15.1.61903.01040
If you manage to run the ssis packages generated in VS2017 on your 2012 server you may use the above - on that case you can use either V4 or V5 of attunity driver as both are recognized by VS2017
you will still need to install Attunity V2.0
read
http://blogs.adatis.co.uk/jonathoneveoconnor/post/Setup-Of-Oracle-Attunity-Connectors
Caveat: As your setup is one that I haven't tried yet you may be required to install SSDT-BI for VS 2012 - you will need to try it out, and if there is an issue it will be on the deployment side, not on VS.
https://www.microsoft.com/download/details.aspx?id=36843 (SSDT-BI for VS 2012)
For your case I advise the following setup - taking in consideration that 2017 has issues
And atke in consideration the caveat above
SSDT 16.5 (VS2015) - https://go.microsoft.com/fwlink/?LinkID=832308
and install both of these
V2.0 - for SSIS 2012
https://www.microsoft.com/en-us/download/details.aspx?id=29283
V4.0 - for SSIS 2016 - SSDT 13.xx (VS 2015)
https://www.microsoft.com/en-us/download/details.aspx?id=52950
Download links
Installer should be the 64 bit version if Windows is 64bit or 32 bit otherwise.
Installing the 64bit also installs a 32bit version
Attunity
V2.0 - for SSIS 2012
https://www.microsoft.com/en-us/download/details.aspx?id=29283
V3.0 - for SSIS 2014 - SSDT 12.xx (VS 2013)
https://www.microsoft.com/en-gb/download/details.aspx?id=44582
V4.0 - for SSIS 2016 - SSDT 13.xx (VS 2015)
https://www.microsoft.com/en-us/download/details.aspx?id=52950
V5.0 - SSDT 14.xx (VS 2017)
https://www.microsoft.com/en-us/download/details.aspx?id=55179
April 7, 2019 at 8:51 pm
Thank you again for the continued expertise-sharing!
I have no idea on the still-old version of SQL Server. I'm not involved with any of the decisions on upgrades/etc. We are about a 95% Oracle shop but do have SQL Server to support what I understand are a few legacy applications we have that require a SQL Server back-end. I think the "upgrade" was from 2008 to 2012.
I'll check with the gentleman that oversees our SQL Server databases Monday a.m. to see if our version is (hopefully!) the Enterprise version.
In Visual Studio 2017 if I search for the particular components that are installed, I of course see that SSIS & SSDT are installed. I believe I need to remove the checkmarks from those items on order to remove them from VS 2017 before I attempt to install the specific version of SSDT you recommend via the EXE-based installer (???), as all of the attempts I've made in recent days to install a different version of SSDT have failed... I think that's probably because SSDT was already present/installed via VS 2017.
Many THANKS again for your detailed help!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply