0845 643 64 63

Monthly Archives: January 2022

How to resolve SSIS “Row yielded no match during lookup” error.

Have you ever been faced with the SSIS error “Row yielded no match during lookup”? If so, this blog is for you!

A customer of ours recently faced the same issue in one of their SSIS packages and asked us to investigate this for them. Initial investigations on their side highlighted that when they replicated the lookup component using a standard join in T-SQL (similar to the image below) it returned the expected results.

So why was SSIS reporting an error and ultimately causing the package to fail?

To demonstrate why they faced this problem, I will provide a worked example. It should be noted that the customer example we were presented with was many thousands of rows, so the problem was much less obvious than the one I have created today.

To begin with, I have created 3 tables in my demo database using a subset of Adventure Works data.

These are:

“example.Product” which contains 10 rows and 4 columns. Essentially this could be thought of as my Product Dimension.

“example.Sales” which contains 40 rows and 4 columns. Essentially this could be thought of as my Staging Fact Table.

And finally, “example.FactSales” which contains 40 rows and 4 columns and is the result of the lookup component and ultimately would form the basis of my Sales Fact Table.

Here you will notice that “ProductNumber” has been replaced with “ProductKey”. Behind the scenes in my example SSIS package I am looking up “ProductNumber” from the “example.Product” table to return “ProductKey” in my Sales Fact Table.

*Note: I have specified for the component to fail if it finds any none matching rows.

Below is the data flow task within my example SSIS package.

This package executes without failure and populates my “example.FactSales”. However, I will now replicate the issue our customer in question faced.

To begin I executed the below T-SQL update statement against my “example.Product” table.

This changed the “ProductNumber” from “AR-5381” to “ar-5381” where “ProductKey” was equal to 1. When performing a simple “SELECT TOP 100 * FROM example.Product” we can now see that “ProductNumber” is lower case where “ProductKey” = 1 and the rest of the Product Numbers remain unchanged.

Now when I replicate the existing SSIS package using a standard T-SQL join as before there appears to be no issue – 40 rows are still returned using an inner join.

However, if I now execute the SSIS package the error “Row yielded no match during lookup” is subsequently returned.

So why is this happening?

Quite simply SSIS is case sensitive, accent sensitive and kana sensitive (at least in full cache mode) whereas SQL Server is not.

Now this is a fairly obvious example, but with more complex lookups which may involve multiple columns, it becomes a little more tricky to spot!

To solve this customer example, I used the EXACT function in MS Excel to spot the exact row in the lookup table which was returning the error. The EXACT function compares two text strings and returns “TRUE” if they are exactly the same and “FALSE” if they are not.

Alternatively, you could use SQL Server itself to replicate the lookup behavior of SSIS. Taking the T-SQL script from before, you can alter the existing inner join to a left join and collate using “Latin1_General_BIN” to identify the potential root cause of the lookup failure.

This highlights the importance of cleaning and standardising your lookup columns before using an SSIS lookup data flow component. Here at Purple Frog we tend to use a scalar function to create clean derived columns which are subsequently used for lookup components.

I hope this helps you solve any “Row yielded no match during lookup” errors with your SSIS solution!

Azure Backup for Virtual Machines

Configuring Backups

Backups are configured for each VM individually, selecting their own retention policies and routines. They can however utilise the same storage and vaults.

1. Select the Backup option under “Operations” in the sidebar of the VM management page, on https://portal.azure.com/

2. Give your backup Vault a name, (where the backups will be stored), and select the resource group you’d like it to be in.

3. Select Edit this policy, (or select a policy if you’ve already configured one you want). Give the policy a better name and set the schedule.

4. Set the Instant Restore duration. (This retains snapshots along with the disks to allow for a faster restore).

5. Choose your different retentions as required. (Only daily or a variety of all?)

6. Optional! Azure creates a resource group for the Instant Recovery points. Enter your own name if you wish.

7. Click OK and then click Enable Backup.

8. Navigate back to the same location after the backup should have taken place, to confirm that you have backups as you’d expect. (Screenshot from an existing backup routine that runs at 7:30pm).

Restoring backups

There are two options when restoring, “Create new” or “Replace existing”. I’ll go over both options below, (the first few steps are the same).

Azure Storage Account (Gen1)

1. Select the Backup option under “Operations” in the sidebar of the VM management page, on https://portal.azure.com/

2. Select the Restore VM option from the top bar.

3. Click Select to choose your restore point.

4. Select the one you wish to restore and click OK.

5. Select your Restore Configuration, either Create new (6. onwards) or Replace existing (8. onwards).

Create new

6. a. Select Restore Type “Create new virtual machine”. (This will create a whole new VM with the same setup as the previous backup, including all disks).

  • Type unique Virtual machine name
  • Select Resource group
  • Select Virtual network
  • Select Subnet
  • Select Staging Location (This is where the data will be copied to out of the backup vault, while the resources are created).

6.b. Select Restore Type “Restore disks”. (This will restore the disks to your resource group that you can then swap out for the existing disks on an existing VM).

  • Select Resource group
  • Select Staging Location (This is where the data will be copied to out of the backup vault, while the resources are created).

7. Select Restore

Replace existing

8. (This will replace the disks on the existing VM, but it will take a new snapshot of that VM before it replaces them).

  • Select the Staging Location. (This is where the data will be copied to out of the backup vault, while the resources are created).

9. Select Restore

After Restore

Create new

a. If you created a new VM, you’ll now need to log in and disable anything that you don’t want running. (Think overnight ETL etc).
b. If you created new disks, you’ll now need to edit an existing VM and replace the disks by going to “Disks” on the VM and selecting the restored disks. (You can swap and swap back easily, so this can be used to pull files from a previous day).

Replace existing

This will take a snapshot of the existing disks before replacing them, so if you want to undo the replacement, you’ll need to go through the restore process again, replacing with that newer restore point.

If you have any questions about the process, please comment below!

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Lewis Prince
Reiss McSporran
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon