0

We move data from Oracle 11 to SQL 2014 using SSIS project deployment model. We use Attunity 3.0 connector.

Connection string to oracle data source is a project parameter and is also stored in a table in SQL.

We use custom stored procedure that

  • Gets this connection string stored in the sql table
  • sets project parameters (via [SSISDB].[catalog].[set_execution_parameter_value] )
  • executes packages (via [SSISDB].[catalog].[start_execution] )

We use [SSISDB].[internal].[execution_parameter_values] to check that parameter values are being replaced during run time with the connection string we stored in the backend.

What's interesting is that, even though Oracle connection string is being replaced during runtime, the package still tries to use the connection string it has been complied with(Project Params). We do not have the same issue when connecting to a SQL Source in a similar fashion.

Do you have any suggestions? Is it a known issue?

Sri
  • 111
  • 1
  • 4

1 Answers1

0

Found the solution. Turns out that the oracle connection string that we stored in the table did not prefix the server name with "SERVER = ". The connection string would straight away start with For Eg - 'x1abc01.something.com:1234/x1abc01;ORACLEHOME=;ORACLEHOME64=;WINAUTH=0;'. Changed the connection string to 'SERVER = x1abc01.something.com:1234/x1abc01;ORACLEHOME=;ORACLEHOME64=;WINAUTH=0;' and it started working now. We tested it by deploying the ssis solution with one connection string and changing it with a different connection string from the database and the overwritten value persists.

However, its still bizarre where the disconnect happens when the run time connection string has an invalid value and its not reported out as an error and ssis quietly switches to design time value in Project Param.

Sri
  • 111
  • 1
  • 4