question

JefferyHill6537-8262 avatar image
0 Votes"
JefferyHill6537-8262 asked YitzhakKhabinsky-0887 commented

Deployment to SQL server 2014 on Windows server 2012 failure due to Microsoft Connector for Oracle

This is a follow up question to the one I posted for the running out of memory issue. It was suggested that I should use Microsoft Connector for Oracle as opposed to the installed OLEDB oracle connection.

I installed the connector used it in the dataflow task and executed the project. Everything works absolutely perfect in Visual Studio. The problem is when I tried deploying the project I get the following error:

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.IntegrationServices.Common.ObjectModel.SqlHelper.ExecuteSQLCommand(SqlStoreConnection storeConnection, CommandType cmdType, String cmdText, SqlParameter[] parameters, ExecuteType execType, Int32 commandTimeout)
at Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder.DeployProject(String projectName, Byte[] projectStream)
at Microsoft.SqlServer.IntegrationServices.Wizard.Common.Model.DeployExportProjectHelper.DeployProject(CatalogFolder folder, Project project)
at Microsoft.SqlServer.IntegrationServices.Wizard.Common.Model.DeploymentModel.Deploy(CatalogFolder folder, Project project)
at Microsoft.SqlServer.IntegrationServices.Wizard.Common.Model.DeploymentModel.DeployProject()
at Microsoft.SqlServer.IntegrationServices.Deployment.DeployProjectPage.DoWork(Object sender, DoWorkEventArgs e)
at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

The SQL Server is on a different server than Visual Studio. I do understand that the connector must also be installed there as well. The issue is compatibility.
We are using Visual Studio 2017 with SSDT 2017.
We are using SQL Server 2014 on a Windows 2012 server.

The documentation says to use the Microsoft Oracle Connection in conjunction with the Microsoft Oracle Adapter 3.0 by Attunity for SQL Server 2012.
However the prerequisites seems to indicate that Microsoft Oracle Connection can only be installed on Windows Server 2016 and above (Not sure if that is the case).

My question then is, can we use the Microsoft Connector for Oracle on a SQL Server 2012 installed on a Windows Server 2012. Our DBA is concerned about putting in on.

If this can't be done, that I have my original issue of running out of memory and need another solution.

There is no immediate plans to upgrade the windows server nor the sql server versions.

sql-server-integration-serviceswindows-server-2012
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 commented

Hi @JefferyHill6537-8262,

(1) SSIS Microsoft Oracle Connector will work on SQL Server/SSIS version 2019 Enterprise Edition and later. Full requirements are documented here:

Microsoft Oracle Connector

(2) If you are on the SQL Server 2012, you need to use Microsoft Attunity driver v.2.0:
SQL Server 2012: Microsoft Connector Version 2.0 for Oracle by Attunity


· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Sorry I made a mistake we are using SQL Server 2014 not 2012, so I understand we need v.3.0.

I'm confused on what is required. The documentation seemed to imply that we had to install both the Microsoft Oracle Connector and the Attunity driver. I guess I misread it.

So all I need is the Attunity on both the server and visual studio, that will give me access to the Oracle Source object in Data Flow and properly deploy to the server?

0 Votes 0 ·

@JefferyHill6537-8262,

So all I need is the Attunity on both the server and visual studio, that will give me access to the Oracle Source object in Data Flow and properly deploy to the server?

Correct. And an Enterprise edition of SQL Server/SSIS 2014 on the server.

0 Votes 0 ·

That's great. Thank you for your help

0 Votes 0 ·

Sorry to bother you again. Our dbas are a little confused on what is required on what.
Right now in our Development environment we are using Visual Studio 2017 with SSDT 2017 installed. As well I installed the version 3.0 of the Attunity driver.

We have a Windows Server 2012 with SQL Server 2014 and SSIS 2014 on it.

Our DBAs are of the impression that we require SSDT installed on the windows server hosting SQL Server.

I was under the impression that in addition to SSIS we only require the version 3.0 of the Attunity driver.

Can you clarify exactly what is needed on each machine.

Thanks,
Jeff

0 Votes 0 ·
Show more comments