0

I have created an SSIS package to transfer data from MySQL to PostgreSQL, and it is working fine on my local system. However, when I try to deploy the package to a SQL Server and schedule a job, it fails to establish a connection with the PostgreSQL server. I have installed all the required drivers on the SQL Server where I am trying to deploy the package.

Please help me identify and other potential solutions to resolve the connection problem.

error-

Executed as user: ERQCINRDB08\SSISUser. Microsoft (R) SQL Server Execute Package Utility Version 14.0.1000.169 for 32-bit Copyright (C) 2017 Microsoft. All rights reserved.
Started: 1:22:27 PM
Error: 2023-07-20 13:22:27.85
Code: 0xC0016016
Source: MySql_To_PG_file
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error

Error: 2023-07-20 13:22:27.91
Code: 0xC0014020
Source: MySql_To_PG_file Connection manager "10.107.3.6.demo.postgres"
Description: An ODBC error -1 has occurred. End Error

Error: 2023-07-20 13:22:27.91
Code: 0xC0014009
Source: MySql_To_PG_file Connection manager "10.107.3.6.demo.postgres"
Description: There was an error trying to establish an Open Database Connectivity (ODBC) connection with the database server. End Error

Error: 2023-07-20 13:22:27.91
Code: 0x0000020F
Source: Data Flow Task 1 ODBC Destination [40]
Description: The AcquireConnection method call to the connection manager 10.107.3.6.demo.postgres failed with error code 0xC0014009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. End Error

Error: 2023-07-20 13:22:27.91
Code: 0xC0047017
Source: Data Flow Task 1 SSIS.Pipeline
Description: ODBC Destination failed validation and returned error code 0x80004005. End Error

Error: 2023-07-20 13:22:27.91
Code: 0xC004700C
Source: Data Flow Task 1 SSIS.Pipeline
Description: One or more component failed validation. End Error

Error: 2023-07-20 13:22:27.91
Code: 0xC0024107
Source: Data Flow Task 1
Description: There were errors during task validation. End Error

DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:22:27 PM Finished: 1:22:27 PM Elapsed: 0.156 seconds. The package execution failed. The step failed.

I want to know complete deployment process for postgrs ssis package.

Shadow
  • 33,525
  • 10
  • 51
  • 64
RONIT WAJE.
  • 1

1 Answers1

1

The error message sounds like you did choose store the password for you database connection in your SSIS package. This will cause to encrypt the password with your local machine key. Running the package on any other machine will cause the decryption to fail.

The preferred way to handle passwords/secrets for SSIS packages is to parameterize the connection and pass over the secret value for an SSIS catalog environment variable.

Another option might be to use integrated security and run the package with a trusted user.
This will require to configure an SQL Server Credential that can be used to run the package when setting up an SQL Server Agent job.

Filburt
  • 17,626
  • 12
  • 64
  • 115
  • same error found – RONIT WAJE. Jul 21 '23 at 08:05
  • not clear with sql server credential – RONIT WAJE. Jul 21 '23 at 08:06
  • When setting up for Integrated Security, you need to make sure a few things are aligned: Your target PostgreSQL instance has to allow login with integrated security and needs to trust your SQL Server machine (Trust for delegation). Configure an SQL Server Credential for a domain user with according privileges on your target PostgreSQL instance. Configure a SQL Server Proxy user that uses the Credential. Configure your SQL Agent job to run under the Proxy user. – Filburt Jul 21 '23 at 09:21
  • For the first alternative (Connection parameters) see [Configure environment specific parameters in SSIS Catalog](https://stackoverflow.com/questions/61062225/configure-environment-specific-parameters-in-ssis-catalog) on setting up environment variables. – Filburt Jul 21 '23 at 09:29