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.

Send the mail to DBA Team about Database Status OFFLINE/ONLINE

 DBA team is Checking manually database status ONLINE and OFFLINE. It is the tedious task. We have automated this task to read the database information and sending status mail to the DBA team.   

SQL Script to get the database status.  

We are developing the package to pull the database information and send it to DBA team mail.  

SQL Script. 

We are storing the full result set into the Object variable.   

Now we are taking the Script task to send the mail.

We are using the below Namespace

using System; using System.Data; using Microsoft.SqlServer.Dts.Runtime; using System.Windows.Forms; using System.Data.OleDb; using System.Net.Mail; using System.Net;

 

Code.

public void Main()         {               DataTable dt = new DataTable();             var oleDa = new OleDbDataAdapter();             oleDa.Fill(dt, Dts.Variables["User::Database_Details"].Value);             string smtpAddress = "smtp.gmail.com";             int portNumber = 587;             bool enableSSL = true;             string emailFromAddress = "bageshkumarbagimsbi@gmail.com"; //Sender Email Address             string password = "************"; //Sender Password             string emailToAddress = "bageshkumarbagi@gmail.com"; //Receiver Email Address             string subject = " Database Status Report";              string body = " ";              body = "Hello DBA Team,";           body = body + Environment.NewLine + Environment.NewLine + " Report deatil as below.";              using (MailMessage mail = new MailMessage())             {                 mail.From = new MailAddress(emailFromAddress);                 mail.To.Add(emailToAddress);                 mail.Subject = subject;                 mail.Body = body;                  mail.IsBodyHtml = true;                  mail.Body += " <html>";                 mail.Body += "<body>";                 mail.Body += "<table border=2>";                 mail.Body += "<tr>";                 mail.Body += "<td bgcolor=#00FFFF>Server name</td><td bgcolor=#00FFFF>DataBase Name</td>" +                   "<td bgcolor =#00FFFF>Status</td>";                 mail.Body += "</tr>";                  foreach (DataRow row in dt.Rows)                 {                     mail.Body += "<tr>";                     if (row.ItemArray[2].ToString() == "ONLINE")                     {                         mail.Body += "<td>" + row.ItemArray[0].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[1].ToString() + "</td>";                         mail.Body += "<td>" + row.ItemArray[2].ToString() + "</td>";                     }                     else                     {                         mail.Body += "<td bgcolor=#FF0000>" + row.ItemArray[0].ToString() + "</td>";                         mail.Body += "<td bgcolor=#FF0000>" + row.ItemArray[1].ToString() + "</td>";                         mail.Body += "<td bgcolor=#FF0000>" + row.ItemArray[2].ToString() + "</td>";                      }                        mail.Body += "</tr>";                  }                  mail.Body += "</table>";                 mail.Body += "<br/><br/>";                 mail.Body += "Thank and Regards,<br/>";                 mail.Body += "DBA Team <br/>";                 mail.Body += "XYZ</body>";                 mail.Body += "</html>";                  using (SmtpClient smtp = new SmtpClient(smtpAddress, portNumber))                 {                     smtp.Credentials = new NetworkCredential(emailFromAddress, password);                     smtp.EnableSsl = enableSSL;                     smtp.Send(mail);                 }             }              Dts.TaskResult = (int)ScriptResults.Success;         }

Now we are running this package. 

Package executed successfully.

See the inbox.  

Opening the mail.  

If the Database is OFFLINE then that row will be RED highlighted. See below  

Get the expected Mail.

Write the multiple CSV files into a single CSV file using SSIS

 We are getting the number of CSV Files we need to merge the all CSV files into a single CSV file.

Below are the files. 

To merge these files we are using the Script task.

Below is the code to merge these files.

try             {                         //Destination file name                 string FileFullPath = @"J:\SSIS1\CSV_Files\Merge_File.csv";                  int counter = 0;                  //Looping through the flat files                 string[] fileEntries = Directory.GetFiles(@"J:\SSIS1\CSV_Files", "*" + ".csv");                 foreach (string fileName in fileEntries)                 {                      string line;                      System.IO.StreamReader SourceFile =                     new System.IO.StreamReader(fileName);                      StreamWriter sw = null;                     sw = new StreamWriter(FileFullPath, true);                      int linecnt = 0;                     while ((line = SourceFile.ReadLine()) != null)                     {                         //Write only the header from first file                         if (counter == 0 && linecnt == 0)                         {                             sw.Write(line);                             sw.Write(sw.NewLine);                          }                         //Write data records from flat files                         if (linecnt != 0)                         {                             sw.Write(line);                             sw.Write(sw.NewLine);                          }                         linecnt++;                         counter++;                     }                      sw.Close();                     Dts.TaskResult = (int)ScriptResults.Success;                 }              }              catch (Exception ex)             {              Dts.Events.FireError(0, "Fire Error", "An error occurred: " + ex.Message.ToString(), "", 0);                 Dts.TaskResult = (int)ScriptResults.Failure;             }

Running this package.

Package executed successfully.

See the file below.  

Data in file. 

Thanks.