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

Balaji Pooruli 1

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.

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

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?

Jack J Jun 24,496 Reputation points Microsoft Vendor

2021-09-14T05:35:51.807+00:00

@Balaji Pooruli , Could you provide the related code about your c# script? Which database you want to connect to, Access or Sql server? Aslo, I think it will be better to provide the specific connection string.
Balaji Pooruli 1 Reputation point

2021-09-14T06:25:41.037+00:00
@Jack J Jun , please find the code snippet below.

SqlConnection myADONETConnection = new SqlConnection(); myADONETConnection = (SqlConnection)(Dts.Connections["$Package::OLE_SRC_MyDatabase"].AcquireConnection(Dts.Transaction) as SqlConnection);

Connection string is as shown below.

Data Source=VXXSAMXXX01\DEV1;Initial Catalog=SAMXX_DEV;Integrated Security=SSPI

The database that I am trying to connect to is MS SQL Server 2016. I am using ADO.NET connection manager with provider as ".NET Providers\SQL Client Data Provider".

Hope this helps.
Jack J Jun 24,496 Reputation points Microsoft Vendor

2021-09-14T07:44:15.12+00:00

@Balaji Pooruli , As usual, your connection string is correct. But the error indicates that your connection string has the problem. Do you use Windows Autherntocation or SQL Server Autherntocation? Please try to use your visual studio to check your connection string is correct. Please choose Tools->Connect to DataBase->Choose DataSource ->input server name->select database->Click TestConnection. If you received a message box called Test Connection Succeed, you could click the Advanced to see the DataSource property, which is the connection string. You can compare with your connection string to check if they are the same.
Balaji Pooruli 1 Reputation point

2021-09-14T08:55:52.627+00:00

@Jack J Jun Thanks for your response. I am using Windows Authentication to connect to SQL server DB.

I tried the steps you have mentioned. I get a message saying "Test Connection succeeded.". When I click on the "Advanced" button, I see the data source property as shown below.

Data Source=VXXSAMXXX01\DEV1;Initial Catalog=SAMXX_DEV;Integrated Security=True

The only difference I see is that the Integrated Security is set to True instead of SSPI. If I hard code the connection, it works fine in the job. However, I need to parameterize the connectionstring as I need to supply different values when deploying to DEV, QA and PROD SSIS server. I will be using TFS build and release pipelines to deploy the job into multiple servers. The parameterized connection string will be supplied via release definition variables.
Jack J Jun 24,496 Reputation points Microsoft Vendor

2021-09-14T09:04:00.547+00:00

@Balaji Pooruli , I recommend that you could define a global variable to parameterize the connectionstring so that other apps can access the connection string.
Balaji Pooruli 1 Reputation point

2021-09-14T09:22:46.687+00:00

@Jack J Jun Can you please provide me an example? I am not an SSIS developer, I am a Salesforce person and hence finding it somewhat tough. I know how to define package-params and user-params and project-params. How do I declare a global variable to parameterize the connectionstring? How can I use it to obtain the DB connection. I see that variables can be obtained using Dts.Variables["$Package::var"].Value. However, if I use a parameter with Dts.connections, I am always getting an error message at run time. I tried with the package level param as SAMDBConnection with the statement Dts.Connections["$Package::SAMDBConnection"].AcquireConnection(Dts.Transaction) as SqlConnection and it always the below error message shown in the OP at run time.

1 answer

Jack J Jun 24,496 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.
Please sign in to rate this answer.
Balaji Pooruli 1 Reputation point

2021-09-16T10:46:11.79+00:00

Thanks, but how do I override this value from the release pipeline? I want to be able to supply different connection strings for Dev, QA and PROD servers.

Jack J Jun 24,496 Reputation points Microsoft Vendor

2021-09-23T02:57:49.157+00:00

@Balaji Pooruli , Sorry for the late response, I am not familiar with the release pipeline, it beyonds my scope. Maybe you could ask the question in the Stack overflow.
Sign in to comment

Share via

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

1 answer