Shahzad Khan – Page 68 – My Point of View

Cross Apply and Outer Apply

Microsoft has introduced APPLY operator in SQL Server 2005. It allows joining between two table expressions, for example joining left/outer table expression with right/inner table expression. The way it works is that we have a table-valued expression on the right side and we want this table-valued expression to be evaluated for each row from the left table expression.

An ideal use case would be where we are unable to form any kind a join between two tables. For example a project table and calendar table.

(Left Side Table – Project Table)

ProjectKey ProjectEndDate

XX-ABC10 2018-10-31

XX-ABC11 2018-11-30

XX-ABD12 2019-01-31

XX-ABC13

(Right Side Table – Calendar Table)

FiscalQuarterStartDate FiscalQuarterEndDate

2018-10-01 2018-12-31

2019-01-01 2019-03-31

Each ProjectEndDate falls between a FiscalQuarter. We need to append a new column in our result set that will be FiscalQuarterEndDate.

Here is the query;

SELECT p.ProjectKey, p.ProjectEndDate, dates.FiscalQuarterEndDate 
FROM Project p
CROSS APPLY
(
	SELECT FiscalQuarterEndDate 
	FROM FiscalCalendar calendar
	WHERE 1=1
	AND p.ProjectEndDate 
	BETWEEN calendar.FiscalQuarterStartDate AND calendar.FiscalQuarterEndDate
) dates
WHERE 1=1

Here is the result;

ProjectKey ProjectEndDate FiscalQuarterEndDate

XX-ABC10 2018-10-31 2018-12-31

XX-ABC11 2018-11-30 2018-12-31

XX-ABD12 2019-01-31 2019-03-31

If we look at the result set, it returns only those rows that matches with the right table expression. Project Number XX-ABC13 is missing. CROSS APPLY is equivalent to an INNER JOIN. To be more precise its like a CROSS JOIN with a correlated sub-query).

If we want to return all rows from the left side then we need to apply OUTER APPLY which is equivalent to a LEFT OUTER JOIN.

Resources

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

How to Add a custom field in Azure DevOps

Azure DevOps has a list of data columns that is not displayed on detail sheet. Try to figure out the column exists before adding a new column,

We would like to add a new column, “Acceptance Criteria”.

You can click on three dots (…) in the upper right corner of task detail and select Customize.

This will take you to the Process section of Organization settings page. Alternatively, you can cick on Organization Settings -> Boards -> Process.

Before adding new column;

Click on “New field”. This will pop up a new window;

Since the field is already created so I am using “Use an existing field” option. You can create a new field if it does not exist.

If you want to make it as required field, click on “Options”.

I just left Layout as default.

Click on “Add field” button.

This field is added. Go back to your issue and you should see something like this;

Hope this will save some time.

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);
}