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, Action1 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, TaskCompletionSource1 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.