Connecting to ADO.Net DB connection in C# script task with variables

Balaji Pooruli 1 Reputation point
2021-09-14T04:54:36.687+00:00

Can some one help me with the code snippet to connect to ADO.Net DB connection from C# script task? When I parameterize the connection string using a package level variable, it appears that the DBConnection is not evaluated and hence shows with a red arrow mark as shown below.

131793-db-status.png

Also, when I double click on the connection and click on 'Test Connection' button, I am getting the below error message.

131794-db-error.png

I am not finding any examples of C# script task that is connecting to ADO.Net DB Connection using variables for the connection string. Can someone help?

.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,545 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,494 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Jack J Jun 24,476 Reputation points Microsoft Vendor
    2021-09-16T08:10:25.9+00:00

    @Balaji Pooruli , I suggest that you could define a global variable in your class.

    Like the following:

    public class DbHelper  
        {  
            public readonly static string connstr = "Data Source=VXXSAMXXX01\DEV1;Initial Catalog=SAMXX_DEV;Integrated Security=True";  
        }  
    

    Then, you could access the string directly:

     SqlConnection connection = new SqlConnection(DbHelper.connstr);  
    

    If the response is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.