Resoved: Error Creating Storage Event Trigger in Azure Synapse

My client receives external files from a vendor and wants to ingest them into the Data Lake using the integration pipelines in Synapse (Synapse's version of Azure Data Factory). Since the exact time the vendor sends the files can vary greatly day-to-day, he requested that I create a Storage Event Trigger.

I quickly set up the trigger:

Set Type to "Storage events", then select the correct storage account and container from the list associated to the current Azure Subscription. Use the blob path begins with setting to filter on the correct folder where the files land and use the blob path ends with setting to filter the file types (and perhaps file names if it's relatively consistent) to ensure that only the right blobs invoke the trigger. Finally, set Event to "Blob created".

Click Continue to go to the next page.

There will be a warning, "Make sure you have specific filters. Configuring filters that are too broad can match a large number of files created/deleted and may significantly impact your cost." which reminds you to check that the filters on the previous page actually return only the desired set of files. Be sure that you have at least one qualifying file in the folder and that the Data Preview can find it. If not, go back to previous page and adjust the the blob path begins with setting and the blob path ends with setting to correct the filtering. 

Click Continue to go to the next page.

This final page asks for the pipeline parameters to use when the trigger is invoked. 

Click Save to create the trigger. 

Once the trigger has been saved, publish the data factory.

So far so good. 

Then this popped up: 

The trigger needs to create and subscribe to an Event Grid event in order to be activated. Even the error was mysterious: 

"The client {GUID}' with object id {GUID}' does not have authorization to perform action 'Microsoft.EventGrid/eventSubscriptions/write' over scope '/subscriptions/{GUID}/resourceGroups/{resourceGroup}/providers/Microsoft.Storage/storageAccounts/{StorageAccount}/providers/Microsoft.EventGrid/eventSubscriptions/{GUID}' or the scope is invalid. If access was recently granted, please refresh your credentials."

I tried numerous searches on how to get the authorization to perform action Microsoft.EventGrid/eventSubscriptions/write and kept hitting dead ends. 

Finally, I started poking around in the Subscription settings to see if something needed to be set in there. Under "Resource Providers", I found that Microsoft.Synapse, Microsoft.Storage, Microsoft.DataLakeStore and Microsoft.EventGrid were all registered. So that felt like a dead end. 

After a bit more muddling around searching, I entered "Failed to Subscribe" in the search and found my savior: Cathrine Wilhelmsen.  She had experienced exactly the same issue and had the same difficulty I had locating information on how to resolve issue. She even mentioned the same articles that I read in my attempts to figure out what to do! The only thing I had not done was visit the Microsoft Q&A thread about running event triggers in Synapse - probably because I stumbled upon her blog post first! Thank you, Cathrine!!

So what was the magic trick?

The Microsoft.DataFactory resource provider was not registered. 

I hadn't expected that because we didn't have Azure Data Factory installed in this subscription, but now we know that it is required for event triggers.

Once the Admin registered Microsoft.DataFactory, I was able to successfully publish the storage event trigger. 😀 

3 Cloud Seeking Awesome Azure People

My company is hiring!!

3Cloud is a technology services firm helping clients transform their business through the power of the cloud. We leverage Microsoft Azure to help clients speed up innovation, operate more efficiently and be more responsive to their client’s needs. As a Microsoft partner, we specialize in Azure migration, cloud-scale custom application development, Internet of Things, analytics, and DevOps achieved through Azure-enabled infrastructure automation. 3Cloud is headquartered in Chicago with additional offices in Dallas and Tampa. 
Get your Azure on and join an awesome team with people like me. 

These are remote roles based in any US city.

Use the link below to see what roles are open for hiring. https://grnh.se/a6fd7f562us

Converting Views to Tables in SQL

Every once in a while, I have a data conversion project in which they want to move data to another server and change a number of legacy views into tables. This is most common when the views use data from linked servers and we have to migrate everything over to Azure SQL Database which doesn't allow cross database queries. 

One could manually write the CREATE TABLE scripts for each, but that's a lot of work. We can leverage the system tables to help us create the scripts from the views. 

Here's my rendition of the script: 

SELECT CONCAT('CREATE TABLE [',s.name,'].[',v.name,']
(',STRING_AGG(CONCAT (
   '[',c.name,'] ',t.name
   ,CASE WHEN t.name = 'varchar' and c.max_length = -1 

         THEN '(max)'

         WHEN t.name IN ('varbinary','varchar')
         THEN CONCAT ('(',convert(VARCHAR(50), c.max_length),')')
 WHEN t.name IN ('decimal','numeric')
 THEN CONCAT ('(',convert(VARCHAR(50), c.precision),',',convert(VARCHAR(50), c.scale),')')
WHEN t.name = 'datetime2'
THEN CONCAT ('(',convert(VARCHAR(50), c.scale),')')
ELSE ''
END
,' '
,CASE WHEN c.is_nullable = 1
      THEN 'NULL'
      ELSE 'NOT NULL'
END), ',') WITHIN GROUP (ORDER BY c.column_id),')

GO ') AS tablename

FROM sys.all_columns c
JOIN sys.types t ON c.system_type_id = t.system_type_id
JOIN sys.VIEWS v ON c.object_id = v.object_id
JOIN sys.schemas s ON v.schema_id = s.schema_id
WHERE v.name IN ('myView1','myView2')  ---put your view names here
GROUP BY s.name,v.name
ORDER BY s.name,v.name

The advantage of my script is that it aggregates all of the text for each table in a single row in the results. The STRING_AGG gracefully handles skipping the comma after the last column, so nothing has to be deleted from the script when copying it to a query window to run, and the CREATE TABLE statement is formatted pretty close to they way most people format their table scripts. I also included the square brackets around the Schema Name, Table Name, and Column Name in  case the legacy views used reserved words in any of those places.  

I'm sure this will come in handy in the future since this is the second project where I've needed this script and I forgot to save it last time.