question

BalajiPooruli-0385 avatar image
0 Votes"
BalajiPooruli-0385 asked JackJJun-MSFT commented

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

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?

sql-server-integration-servicesdotnet-adonet
db-status.png (8.4 KiB)
db-error.png (7.0 KiB)
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@BalajiPooruli-0385, 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.

0 Votes 0 ·

@JackJJun-MSFT , 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.

0 Votes 0 ·

@BalajiPooruli-0385 , 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.

0 Votes 0 ·
Show more comments

1 Answer

JackJJun-MSFT avatar image
0 Votes"
JackJJun-MSFT answered JackJJun-MSFT commented

@BalajiPooruli-0385, 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.




· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

@BalajiPooruli-0385, 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.


0 Votes 0 ·