How to dynamically connect to PostgreSQL, Oracle, and MySQL inside an SSIS Script Task using credentials from a SQL table to use these connection to perform rest of ETL Operations?

Mohammad Qasim 576 Reputation points
2026-06-21T10:18:20.8166667+00:00

Greetings,

I am developing an ETL process using SSIS (SQL Server 2019) and Visual Studio 2022.

My Goal

I need to connect to three external databases (PostgreSQL, Oracle, and MySQL) inside an SSIS Script Task (C#).

The Requirement

To keep the package maintenance-free, I want to store all connection details (IP address, Database Name, User ID, and Password) in a central configuration table inside a SQL Server database.

  • The Script Task must first read these credentials from the SQL Server table.
  • It should then dynamically build the connection strings and open connections to PostgreSQL, Oracle, and MySQL .
  • If an IP or password changes in the future, we will only update the SQL Server table. The SSIS package/Script Task code should not be modified.
  • My Questions
  1. What are the recommended ADO.NET providers or drivers to use inside the Script Task for PG, Oracle, and MySQL in VS 2022?
  2. Can anyone share a clean C# code structure or boilerplate example demonstrating how to fetch these parameters, build the connection strings dynamically, and open the database connections?
  3. After making connection ,using these connection to perform rest of ETL Operation like Sourse and Destinatiion

Thanks

SQL Server Integration Services
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Akhil Gajavelly 1,830 Reputation points Microsoft External Staff Moderator
    2026-06-23T09:21:49.4133333+00:00

    Hi @Mohammad Qasim ,

    What you're describing is possible, but it's generally recommended to separate configuration management from ETL execution.

    In SSIS, a common approach is:

    1. Store the connection details (server, database/service name, user name, password, etc.) in a SQL Server configuration table.
    2. Read those values at package startup (for example using an Execute SQL Task and SSIS variables).
    3. Build the connection strings dynamically using expressions or within a Script Task.
    4. Use the appropriate .NET providers:
      • PostgreSQL: Npgsql
        • Oracle: Oracle Managed Data Access
          • MySQL: MySQL Connector/NET

    Inside a Script Task, you can retrieve the configuration values from SQL Server, construct the provider-specific connection strings, and create the corresponding DbConnection objects (NpgsqlConnection, OracleConnection, MySqlConnection).

    However, if the goal is to perform the actual ETL (extract, transform, and load), consider whether SSIS Connection Managers and Data Flow Tasks can be used instead of implementing the entire data movement logic in C#. This typically provides better maintainability, logging, error handling, and performance than performing all ETL operations inside a Script Task.

    Could you clarify whether you only need the Script Task to retrieve the connection information dynamically, or whether you intend to perform all source extraction and destination loading inside C# code? The recommended design may differ depending on that requirement.

    Thanks,
    Akhil.

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.