Throw exceptions in SQL and C#

This is how we can throw an exception in C#;

static void CopyObject(SampleClass original)
{
    if (original == null)
    {
        throw new System.ArgumentException("Parameter cannot be null", "original");
    }
}

This is how we can throw an exception in SQL;

BEGIN TRY
	SET NOCOUNT ON;

	SELECT 1/0;

END TRY  
BEGIN CATCH  
    --SELECT   
    --    ERROR_NUMBER() AS ErrorNumber  
    --    ,ERROR_SEVERITY() AS ErrorSeverity  
    --    ,ERROR_STATE() AS ErrorState  
    --    ,ERROR_PROCEDURE() AS ErrorProcedure  
    --    ,ERROR_LINE() AS ErrorLine  
    --    ,ERROR_MESSAGE() AS ErrorMessage;  
    THROW;
END CATCH;

If you don’t want to throw exception, comment “THROW” keyword. This will stop propagating exception to calling method and “catch(SqlException ex)” block will never be able to see it.

Uncomment all other lines. You have to use data reader to get result back and handle exception manually.

C# component to read Excel data using OleDb in SSIS

This is how we can read data from Microsoft Excel using OleDb in SSIS;

public override void CreateNewOutputRows()
    {
        //Change this to your filename you do not need a connection manager
        string fileName = @"E:\SFTP\RSS\Results.xlsx";  
        string SheetName = "Sheet1";  
        string cstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";

        using (System.Data.OleDb.OleDbConnection xlConn = new System.Data.OleDb.OleDbConnection(cstr))
        {
           xlConn.Open();
           System.Data.OleDb.OleDbCommand xlCmd = xlConn.CreateCommand();
           xlCmd.CommandText = "Select * from [" + SheetName + "$]"; //I assume this is the data you want
           xlCmd.CommandType = CommandType.Text;
           using (System.Data.OleDb.OleDbDataReader rdr = xlCmd.ExecuteReader())
           {
              while (rdr.Read())
              {
                 for (int i = 4; i < rdr.FieldCount; i++) //loop from 5th column to last
                 {
                    //The first 4 columns are static and added to every row
                    Output0Buffer.AddRow();
                    Output0Buffer.UniqueID = Int32.Parse(rdr[0].ToString());
                    Output0Buffer.Year = Int32.Parse(rdr[1].ToString());
                    Output0Buffer.ReportingWave = rdr.GetString(2);
                    Output0Buffer.SubmissionDate = rdr.GetString(3);
                    Output0Buffer.Question = rdr.GetName(i);
                    Output0Buffer.Answer = rdr.GetString(i);
                 }

            }
          }
          xlConn.Close();
       }
    }

We need to add output columns with correct data type. There is no need to setup connection manager because we are using it in the code.

This code will successfully import a file that looks like this;

My recent experience with Excel Sheet.

Check the data type of the column. If the type is “General”, then ACE will determine data type based on first 8-10 rows. If those are numeric then it might ignore alpha-numeric data in the column. Try to change column data type to “Text” and see what happens. It will work. You would be able to see numeric and non-numeric data in your data pipe line.

Resources;

https://stackoverflow.com/questions/61514481/get-an-ssis-c-sharp-script-component-to-read-from-excel-via-oledb

https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-scripting/data-flow-script-component/configuring-the-script-component-in-the-script-component-editor?view=sql-server-ver15

Send email in .NET Core using Mailkit and office 365

Using SmtpClient to send email .NET core is obsolete. The current recommendation is to use the MailKit library . Here is how to use it with the office 365 SMTP servers.

var message = new MimeMessage();
message.From.Add(new MailboxAddress("{from name}", "{from email address}"));
message.To.Add(new MailboxAddress("{to name}", "{to email address}"));
message.Subject = "{subject}";

message.Body = new TextPart("plain")
{
    Text = "{body}"
};

using (var client = new SmtpClient())
{
    await client.ConnectAsync("smtp.office365.com", 587, SecureSocketOptions.StartTls);
    await client.AuthenticateAsync("{from email address}", "{from password}");
    await client.SendAsync(message);
    await client.DisconnectAsync(true);
}

Difference between mit, gpl and apache software license

The MIT, BSD, and ISC licenses are “permissive licenses”. They are extremely short and essentially say “do whatever you want with this, just don’t sue me.”

The Apache license says “do whatever you want with this, just don’t sue me” but does so with many more words, which lawyers like because it adds specificity. It also contains a patent license and retaliation clause which is designed to prevent patents (including patent trolls) from encumbering the software project.

The GPL licenses (GPLv3, GPLv2, LGPL, Affero GPL) all contain some kind of share-alike license. They essentially say “if you make a derivative work of this, and distribute it to others under certain circumstances, then you have to provide the source code under this license.” The important thing to know here is that “derivative work” and “certain circumstances” both require some legal analysis to understand the meaning and impact for your project.

You can read more about it here;

Which License Should I Use? MIT vs. Apache vs. GPL

Don’t’ fail parent package if child package fails

I like to keep running child packages inside sequence container even if one of them fails. Currently they are failing;

The work around is to set Sequence container “MaximumErrorCount” property to 2 from 1. After doing that, I am still getting failure error.

What we need to do is to fake the result assuming master and child packages have proper logging in place if they fail. Go to the Properties of “ExecPkg – Child Package” and set “ForceExecutionResult” to Success.

Do the same for master package. Run the package;

You can see that child package has no issues despite the fact that this has been failed. We can confirm that from log table if it’s enabled on package level.

These are the default values for a new container.

Now lets stop and study. If we compare the package behavior against the property settings, this looks wrong. Here we have set FailPackageOnFailure=False, yet a Sequence Container failure is causing a Package failure. Why is this? Unintuitive attribute names. See this Microsoft Connect issue. You are not alone in your confusion. The official explanation from Microsoft is this.

Despite some pretty circular previous messages, we believe that the feature is behaving as designed. When you set FailParentOnFailure to false, the parent will not fail until the number of failures in the child exceeds the MaximumAllowedErrors threshold. When you set FailparentOnFailure to true, the parent will fail on the first occurence of an error regardless of the MaximiumAllowedErrors threshold.