I am convinced they got tired of me saying, “Well, you’ll need SSIS if you want to read Excel…” Probably not, though. Either way… score! :{>
Read on to see how easy it is to select Excel as a file format for your Blob Storage data.
Comments closedCategory: Cloud
Mark Kromer meets a challenge:
Today, we don’t have an option in data flows in ADF to include headers when your transformations result in zero rows. But you can build the logic to handle this scenario. So, until we add a checkbox feature to include headers, you can use this technique below to achieve this.
Click through for the explanation, as well as a completed version you can take for your own.
Comments closedPaul Andrew has an update to the Azure Data Factory Procedural Framework:
The primary goal of this release was to implement email alerting within the existing processing framework and using existing metadata driven practices to deliver this in an easy to control, flexible and granular way. That said, the following statements have been met in terms of alerting capabilities and design.
Read on for the full change list.
Comments closedDenny Cherry explains the difference between Azure Storage Accounts and Azure Managed Storage:
Microsoft Azure has two different kinds of storage available, Storage Accounts and Managed Storage. Each of these has its use, and with one exception can’t really be interchanged between each other.
Read on to learn how each is useful in its own way.
Comments closedRayis Imayev has to send an e-mail:
It is a very simple concept, you have a database query result, either a direct database table output or a stored procedure returned result that needs to be sent as email notification. This email could serve as a status report of your daily data processing job or alert notification with some metrics that require immediate attention, and you want to be as user-friendly as possible with this message aka HTML format for your dataset is required.
I’ve built this solution approach over a year go and wanted to document and share it after recently using it in one of my projects. Again, I agree, there other more or less effective ways to achieve the very same goal, this blog post is just a recollection of my efforts to send HTML formatted SQL query results using Azure Data Factory and Logic App.
It’s a bit more effort than sp_send_dbmail, though also considerably more flexible.
Comments closedMatt Robertshaw has a few tips for saving money in Azure:
4. Enterprise Dev/Test subscriptions
Enterprise Dev/Test subscriptions are provided as part of Microsoft Enterprise Agreements (EA). They’re designed for teams of Visual Studio subscribers to run development and test workloads in Azure but at discounted rates, specifically on Windows virtual machines and exclusive gallery images. This is significant because I see many clients using regular Azure subscriptions for development and test, which means they’re potentially paying more than they need to.
One additional point I’d make, though it kind of fits in with points 7 and 9, is to try to make your own services “cloud-first.” In other words, a typical cloud migration takes services built for on-prem data centers, converts the servers into VMs, ships those VMs up into Azure/AWS/Google/whatever, and then you end up paying more than you did on-prem.
Instead, read up a bit on cloud architecture and see how you might be able to change a service to fit that model. Instead of having a server running all the time, is it possible to store messages in a queue and have functions process these messages on a given schedule? Can you use expensive tools like Azure Synapse Analytics to perform nightly data processing and move the results to a much less expensive Azure SQL Database?
Comments closedIn part three of this series I looked at functional tests for ADF pipelines: verifying, in isolation, that pipelines are “doing things right”. In this post I’ll be testing isolated pipelines to check that they’re “doing the right things” – this is one description of a unit test. In a general-purpose programming language, unit tests might be used to verify that an individual line of code is executed, or that it has a particular effect. In Azure Data Factory, the smallest unit of development – a “line of code” – is a pipeline activity. I will be writing tests to verify that specific activities are executed (or not) and to inspect their results.
There’s a fair bit involved in this sort of test.
Comments closedEd Elliott shares several approaches for executing Databricks code from Azure Data Factory:
.Net
No post I write about Spark is going to miss off .Net, if you are writing C# or F# then you can’t use a notebook anyway. If you do this then you will also include the .Net driver JAR file (weird statement to make) and call that.
Read on for all of the techniques.
Comments closedWilliam Assaf diagnoses an issue:
If you’ve tried doing Backup to URL with SQL Server using a Shared Access Signature (SAS) certificate and received this error:
Error: 18204, Severity: 16, State: 1.BackupDiskFile::CreateMedia: Backup device ‘https://account.blob.core.windows.net/container/folder/msdb_log_202005170801.trn’ failed to create. Operating system error 50(The request is not supported.).Cannot open backup device ‘https://account.blob.core.windows.net/container/folder/msdb_log_202005170801.trn’. Operating system error 50(The request is not supported.). [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). NOTE: The step was retried the requested number of times (1) without succeeding. The step failed.
You may have received the same error I encountered.
This error popped up only after startup of SQL Server. To resolve the problem, we’d recreate the SAS key, using the same cert in the same script, and the backups would start working again. This affected all types of SQL database backups.
William did some troubleshooting and explains the core problem. It’s a weird one.
Comments closedWith Power Query you can apply filters to the list obtained by the File System View option, thus restricting the access to only those files (or a single file) you are interested in. However, there is no query folding of this filter. What happens is that every time you refresh the data source, the list of all these files is read by Power Query; the filters in M Query to the folder path and the file name are then applied to this list only client-side. This is very expensive because the entire list is also downloaded when the expression is initially evaluated just to get the structure of the result of the transformation.
A better way to manage the process is to specify in the URL the complete folder path to traverse the hierarchy, and get only the files that are interesting for the transformation – or the exact path of the file is you are able to do that. For example, the data lake I used had one file for each day, stored in a folder structure organized by yyyy\mm, so every folder holds up to 31 files (one month).
Read on for more advice in this vein.
Comments closed