Executing the SP with parameter using Script task in SSIS package

We are using Script task in our package and we want to log the error message on the Error log table. For inserting the records in the Error Log table we have created SP. This Sp we are Using on several Package.

Below is the log table

CREATE TABLE errorlog   (      error_log_id INT IDENTITY(1, 1) NOT NULL,      error_time   DATETIME NOT NULL DEFAULT Getdate(),      username     VARCHAR(100) NOT NULL,      package_name VARCHAR(100) NOT NULL,      task_nm      VARCHAR(100) NOT NULL,      errormessage NVARCHAR(4000) NOT NULL,   )

We are using below Sp to insert the record in this table.

CREATE PROCEDURE Insert_errorlog @UserName     VARCHAR(100),                                  @Package_Name VARCHAR(100),                                  @Task_Nm      VARCHAR(100),                                  @ErrorMessage NVARCHAR(4000) AS   BEGIN       SET nocount ON;        INSERT INTO errorlog                   (username,                    package_name,                    task_nm,                    errormessage)       VALUES      (@UserName,                    @Package_Name,                    @Task_Nm,                    @ErrorMessage)   END go

 Now we are taking Script task.  

We are selecting the some system variables like user name , Package name and Task name.  

Creating the ADO.Net Connection Manager.  

In Script task we are calling the SP in the Catch block if any error occurs then we can log the error information.

public void Main()                                 {             // TODO: Add your code here             try             {                 SqlConnection myADONETConnection =                                     Dts.Connections["Ado_Dot_net_Conn"].AcquireConnection(Dts.Transaction) as SqlConnection;                 Dts.TaskResult = (int)ScriptResults.Success;             }             catch (Exception ex)             {                 SqlConnection myADONETConnection =                     Dts.Connections["Ado_Dot_net_Conn"].AcquireConnection(Dts.Transaction) as SqlConnection;                 SqlCommand cmd = new SqlCommand(("Insert_ErrorLog"));                 cmd.Connection = myADONETConnection;                 cmd.CommandType = CommandType.StoredProcedure;                 cmd.Parameters.Add("@UserName", SqlDbType.VarChar).Value = Dts.Variables["System::UserName"].Value.ToString();                 cmd.Parameters.Add("@Package_Name", SqlDbType.VarChar).Value = Dts.Variables["System::PackageName"].Value.ToString();                 cmd.Parameters.Add("@Task_Nm", SqlDbType.VarChar).Value = Dts.Variables["System::TaskName"].Value.ToString();                 cmd.Parameters.Add("@ErrorMessage", SqlDbType.VarChar).Value = ex.Message.ToString();                 cmd.ExecuteNonQuery();                 Dts.TaskResult = (int)ScriptResults.Failure;             }                 }

In try block we are forcefully producing the error and catching that error into Catch block and inserting the error message to ErrorLog table using SP.  

Generating Error forcefully.

try             {                 SqlConnection myADONETConnection =                                     Dts.Connections["Ado_Dot_net_Conn1"].AcquireConnection(Dts.Transaction) as SqlConnection;                 Dts.TaskResult = (int)ScriptResults.Success;             }

 Let’s running this package. 

Package got failed.

Let’s see the error in the log table. 

Error message.

The connection "Ado_Dot_net_Conn1" is not found. This error is thrown by Connections collection when the specific connection element is not found.

 Ado_Dot_net_Conn1  connection not in this package due to that this package get failed.

Let’s correct this error.

try             {                 SqlConnection myADONETConnection =                                      Dts.Connections["Ado_Dot_net_Conn"].AcquireConnection(Dts.Transaction) as SqlConnection;                 Dts.TaskResult = (int)ScriptResults.Success;             }

 Running this package.

Package executed successfully.