Reading Connection string from ADO.Net Connection manager using Script task and inserting records into the table

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 ADO.Net connection manager in script task.

First of all we are creating the ADO.Net connection manager.

Right Click on the Connection Manager pane and select the New ADO.Net Connection.

 

Creating a connection manager.

 

Click ok.

The connection manager created successfully. 

Now we are taking the script task.

And writing the below code

public void Main()                                 {             SqlConnection myADONETConnection =                 Dts.Connections["Ado_Dot_net_Conn"].AcquireConnection(Dts.Transaction)                 as SqlConnection;             MessageBox.Show(myADONETConnection.ConnectionString, "ADO.NET Connection");               Dts.TaskResult = (int)ScriptResults.Success;                                 }

 Running this task.

Now we are adding code to insert a record in the table.

Below is the code to insert a record into the table.

public void Main()                                 {                                                 // TODO: Add your code here             SqlConnection myADONETConnection =                 Dts.Connections["Ado_Dot_net_Conn"].AcquireConnection(Dts.Transaction)                 as SqlConnection;             SqlCommand cmd = new SqlCommand();// Creating instance of SqlCommand              cmd.Connection = myADONETConnection; // set the connection to instance of SqlCommand              cmd.CommandText = "insert into MyFirstImport (PersonID,FirstName,LastName,BirthDate) values (3,'Bagesh','Kumar','1988-03-01')"; // set              cmd.ExecuteNonQuery();             MessageBox.Show("Record Saved"); // showing messagebox for confirmation message for user              myADONETConnection.Close();// Close the connection               Dts.TaskResult = (int)ScriptResults.Success;                                 }

Before running this task record in the table

   

Now we are running this script.

  

Record saved successfully in the database.

See the records in the database table.  

Get the expected result. J