Sometimes, we may encounter the scenario to load/read the data from database using script task and that time we need to get the connection string from the connection manager to handle this scenario dynamically. In this topic, we are going to look how can we read the connection string from connection manager's connection using script task step by step.
Reading connection string from OLEDB connection using Script Task
Step 1: Edit the Script task and go to Solution Explorer, Right click References and choose Add References..
Step 2: In Reference Manager window, select Extensions under the Assemblies and click Microsoft.SQLServer.DTSRuntimeWrap check box shown in below.
Step 3: Add namespace using Microsoft.SqlServer.Dts.Runtime.Wrapper; in the name space section.
Step 4: Copy and paste below code under the public void main() method shown in below.
Step 2: In Reference Manager window, select Extensions under the Assemblies and click Microsoft.SQLServer.DTSRuntimeWrap check box shown in below.
Step 3: Add namespace using Microsoft.SqlServer.Dts.Runtime.Wrapper; in the name space section.
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.OleDb;
#endregionStep 4: Copy and paste below code under the public void main() method shown in below.
public void Main()
{
//Declaring the variables
string oleDBConnString = string.Empty;
IDTSConnectionManagerDatabaseParameters100 connMrgParameters = default(IDTSConnectionManagerDatabaseParameters100);
System.Data.OleDb.OleDbConnection oledbConn = null;
ConnectionManager connMgr = Dts.Connections["OleDB_ConnectionName"];
//Reading the Connection manager Properties and assigning to variables
connMrgParameters = (IDTSConnectionManagerDatabaseParameters100)connMgr.InnerObject;
oledbConn = connMrgParameters.GetConnectionForSchema() as OleDbConnection;
oleDBConnString = oledbConn.ConnectionString;
Dts.TaskResult = (int)ScriptResults.Success;
}
To view the result, we can place a breakpoints on anywhere in the C# code and get the connection string using debugging mode which shown in above picture.
Reading connection string from ADO.NET connection using Script Task
It's pretty similar to previous method but the Microsoft.SQLServer.DTSRuntimeWrap namespace is not required to read the ADO.NET connection string from connection manager's connection.
Step 1: Edit the Script task and paste below code under the public void Main() method shown in below.
public void Main()
{
//Declaring the variables
string adonetConnString = string.Empty;
SqlConnection sqlConn = new SqlConnection();
sqlConn = (SqlConnection)(Dts.Connections["ADO.NET_ConnectionName"].AcquireConnection(Dts.Transaction) as SqlConnection);
adonetConnString = sqlConn.ConnectionString;
Dts.TaskResult = (int)ScriptResults.Success;
}That's it for reading connection string from ADO.NET connection in connection manager and no need to add reference on script task solution explorer.
this is for script task, may useful for somone.
ReplyDeleteIDTSConnectionManager100 connMgr = this.Connections.ADONetAppStaging ; //this we need to give name in connection manager in script component
SqlConnection myADONETConnection = new SqlConnection();
myADONETConnection = (SqlConnection)(connMgr.AcquireConnection(null));
//Read data from table or view to data table
string query = "Select top 10 * From ##AP_Stagging_Temp_ExportWODuplicates Order by 1,2,3 asc ";
// string query = "Select * From ##AP_Stagging_Temp_For_JLL_ExportWODuplicates order by 1,2,3 asc ";
SqlDataAdapter adapter = new SqlDataAdapter(query, myADONETConnection);
datatable dtExcelData = new datatable();
adapter.Fill(dtExcelData);
myADONETConnection.Close();
This comment has been removed by a blog administrator.
ReplyDeleteNice Post Oracle Cloud Automated Testing Tool
ReplyDelete