Reading Connection string from OLEDB Connection manager using Script task

Some scenarios in the SSIS where we need to use a connection string within in script task. Using this connection string we can insert or update or fetch the records from the tables table.

In this demo we will see how we can use the OLEDB connection manager in script task.

First of all we are creating the OLEDB connection manager.

Right Click on the Connection Manager pane and select the New OLEDB Connection.

  

Creating a connection manager 

Click ok.

OLEDB connection is created successfully.  

Now we are taking the script task.

In the script task, we need to add the below assembly.

“Microsoft.SQLServer.DTSRuntimeWrap”  

Click ok.

We need to add the below namespace in the code.

using Microsoft.SqlServer.Dts.Runtime.Wrapper; using System.Data.OleDb;

 Now in the main we need to write the below code to read the connection string.

public void Main()                                 {           // TODO: Add your code here            string oleDBConnString = string.Empty;             IDTSConnectionManagerDatabaseParameters100 connMrgParameters = default(IDTSConnectionManagerDatabaseParameters100);             System.Data.OleDb.OleDbConnection oledbConn = null;             ConnectionManager connMgr = Dts.Connections["OLEDB_Conn_mgr"];             //Reading the Connection manager Properties and assigning to variables             connMrgParameters = (IDTSConnectionManagerDatabaseParameters100)connMgr.InnerObject;             oledbConn = connMrgParameters.GetConnectionForSchema() as OleDbConnection;             oleDBConnString = oledbConn.ConnectionString;              MessageBox.Show(oleDBConnString.ToString());             Dts.TaskResult = (int)ScriptResults.Success;                                 }

 Now we are running this script. 

Now we are able to read the OLEDB connection string.