Azure Function Deployed But Not Visible

 Playing around with Azure functions lately and struck a weird issue when trying to get the function running up in Azure (as opposed to just testing on my laptop).

 This particular function is a Python script, developed and deployed from Visual Studio Code to an existing Function App. The function behaved correctly in the VS Code dev environment, and appeared to deploy fine with no errors, however when checking in the Azure Portal there was no function to be found. Deleting and recreating the Function App didn't help

The fix was to add "AzureWebJobsFeatureFlags": "EnableWorkerIndexing", to the local.settings.json file, so it looks something like

{

  "IsEncrypted": false,

  "Values": {

    "FUNCTIONS_WORKER_RUNTIME": "python",

    "AzureWebJobsFeatureFlags": "EnableWorkerIndexing",

  }

}

 In addition, the .funcignore file was excluding local.settings.json, so I removed this as well.

Once this was done and the function redeployed everything turned up as expected 

SSIS For Loop AssignExpression Error

 Just a quick note on a frustrating error adding an AssignExpression to an SSIS For Loop Container.

The expression being added was

@[User::LoopCount] = @[User::LoopCount] + 1

which looks fine, but continually gave an error of "the equals (=) sign at position 20 was unexpected". 

Changing the variable formats, and other syntactic changes didn't help. And the cause?

A space at the front of the expression. If I'd counted to position 20 I might have found it earlier, as the = sign was at position 19, but then again, probably not. Removing the space solved the error and allowed the loop to run.

Azure Data Factory Metadata-Driven Pipelines - 2

 As mentioned in the previous post, we're using some custom tables behind our metadata-driven pipeline to provide some flexibility and hopefully allow it to be expandable. The schema is still a work in progress, and has some obvious limitations that could be improved on once we have some more time available.

Overview 

High-level, the solution has some tables to hold the entities/tables that are to be loaded, along with column mappings, "high water" values for delta loads, and any pre-copy scripts to be run. Here's the ERD.

 

 

Table	Purpose
DataFeed	High level data feed details
DataFeedEntity	Tables to load
ColumnName	Column names to cut down duplication in table rows
DataFeedColumn	Source and destination columns
DataFeedEntityScript	The pre-copy script to run if required
HighWaterValue	Column and value used for delta loads

Most of the tables have an "Active" column which provides flexibility of which entities and columns to include in the ADF copy activities.

On top of the tables is a view which is used to present the table data to the ADF pipeline. In theory it should be possible to have multiple views for different data loads, but we haven't tested this yet.The easiest way to create the view is to use the table that the ADF wizard creates then recreate the table output in the view. I'll include our current version below for reference. There's also a stored proc to update the high water values, and a table valued function which gets a count of active entities to be loaded.

Limitations

There're a few baked in limitations in our solution which were design choices based on our loads and keeping it simple (ish) to start with. These should all be easy to adapt and we'll probably look at that in the down time. The key limitations are:

• we assume the destination table has the same name as the source entity
• also assume that the target schema is dbo. This was a bit of laziness and we'll probably add a destination schema column to the DataFeedColumn table in the near future
• the data type columns in DataFeedColumn are the ADF data types, e.g. String rather than varchar. We have a separate mapping table that we use when populating the table, but this could be added to the schema 
• We don't store connections in these tables, which could be a useful enhancement 

Example SQL View

SELECT CONCAT (
            N'{
            "entityName": "',
            dfe.DataFeedEntityName,
            N'"
        }'
            ) AS SourceObjectSettings,

        NULL AS [SourceConnectionSettingsName],

        NULL AS [CopySourceSettings],

        CONCAT (
            N'{
            "schema": "dbo",
            "table": "',
            dfe.DataFeedEntityName,
            N'"
        }'
            ) AS[SinkObjectSettings],

        NULL AS [SinkConnectionSettingsName],

        concat(N'{
            "preCopyScript": ', ISNULL(QUOTENAME(pre.ScriptBody, '"'), 'null') ,
            ',
            "tableOption": null,
            "writeBehavior": ', CASE WHEN dfe.LoadType = 'FullLoad' THEN '"insert"' ELSE '"upsert"' END, ',
            "sqlWriterUseTableLock": true,
            "disableMetricsCollection": false,
            "upsertSettings": {
                "useTempDB": true,
                "keys": [
                    "', keycols.KeyColumnName, N'"
                ]
            }') AS [CopySinkSettings],

        REPLACE(CAST(N'{
            "translator": {
                "type": "TabularTranslator", 
                "mappings": [{X}]
             }
          }' AS NVARCHAR(max)), N'{X}', ca.X) AS [CopyActivitySettings],

        N'MetadataDrivenCopyTask_ftq_TopLevel' AS [TopLevelPipelineName],

        N'[
            "Sandbox",
            "Manual"
        ]' AS [TriggerName],

        CONCAT (
            N'{
            "dataLoadingBehavior": "',
            dfe.LoadType,
            N'",',
            N'"watermarkColumnName": "',
            cm.ColumnName,
            N'",',
            N'"watermarkColumnType": "DateTime",',
            N'"watermarkColumnStartValue": "',
            convert(VARCHAR(40), hv.TimestampValue, 126),
            N'"',
            N'}'
            ) AS [DataLoadingBehaviorSettings],

        dfe.EntityGroup as [TaskId],

        dfe.Active [CopyEnabled],

        ROW_NUMBER() OVER(ORDER BY dfe.[EntityGroup], dfe.[DataFeedEntityId] DESC) AS RowNumber,

        dfe.DataFeedEntityId

    FROM adf.DataFeedEntity dfe
    JOIN (
        SELECT c.DataFeedEntityId,
            STRING_AGG(N'{"source":{"name":"' + cast(c.[SourceColumnName] AS NVARCHAR(max)) + N'",
            "type": "' + c.SourceDataType + '"},"sink":{"name":"' + cast(c.[DestinationColumnName] AS NVARCHAR(max)) + '"}}', ',') X
        FROM adf.vw_ColumnMapping c
        GROUP BY DataFeedEntityId
        ) ca ON ca.DataFeedEntityId = dfe.DataFeedEntityId
    LEFT JOIN adf.HighWaterValue hv ON dfe.DataFeedEntityId = hv.DataFeedEntityId
    LEFT JOIN adf.ColumnName cm ON hv.ColumnNameId = cm.ColumnNameId
    LEFT JOIN (
        SELECT  c.ColumnName as KeyColumnName, 
                dfc.DataFeedEntityId
        FROM [adf].[DataFeedColumn] dfc
        JOIN [adf].[ColumnName] c ON dfc.DestinationColumnNameId = c.ColumnNameId
        WHERE dfc.IsKey = 1
    ) keycols ON dfe.DataFeedEntityId = keycols.DataFeedEntityId
    LEFT JOIN adf.DataFeedEntityScript pre ON dfe.DataFeedEntityId = pre.DataFeedEntityId
        AND pre.ScriptType = 'PreCopy'
    WHERE dfe.Active = 1 

 

 

Part 1: Azure Data Factory Metadata-Driven Pipelines - 1

SSIS Azure SQL Connection Login Failure

 Issue

Running an SSIS package using environment variables failed when trying to connect to an Azure SQL database. The error returned pointed to a password issue with the SQL user.

Solution

There was a couple of issues at play here.

1. The server name component of the connection string needed to have the full Azure database name, i.e. my-sql-server.database.windows.net rather than just my-sql-server
2. We also had to add the Persist Security Info=true parameter to the connection string. I think this is because the user we connect as is a contained user to the specific database.

Also worth noting that as we use a contained user the database needs to be specified in the connection string 

Azure Data Factory Metadata-Driven Pipelines - 1

 I've had a bit of free time work wise lately so figured I'd finally get onto revamping one of our Azure Data Factory (ADF) extract processes. 

This particular one gets data from our Dynamics CRM and exports to the landing stage of our data warehouse. It was stood up in a hurry due to Microsoft retiring the previous, CRM driven, export process, and us internally missing the notifications so that by the time we realised what was happening the drop-dead date was almost upon us.

It's a pretty simple process which just dumps data from selected Dynamics entities into SQL tables - no transforms and very little filtering, and has been pretty stable though we have had to filter a couple of the entities due to size, and also reduce the number of attributes/columns as our customisations have resulted in 2 particular entities having > 500 attributes. 

ADF Metadata-Driven copy task 

What I've decided on is to update this to use the Metadata-Driven copy task, as well as cleaning up the extract attributes and implementing some better filtering to reduce the daily data extract quantity.

There's already a lot of info out there about the metadata-driven task, so I'm not going to get too into it here. Basically it will allow effectively a single process that will cycle through all the entities to be exported, rather than creating multiple datasets and copy tasks. 

I always forget how to get to it though, so here's how. Once you're launched the ADF Studio from the portal you create a new task by creating a new Factory Resource and choosing the Copy Data tool.

 

 This will open a window/blade that lets you select Metadata-driven copy task as the copy task.

A few notes about this - some of my decisions during the create will be explained later:

• It needs an existing database to create the SQL objects needed for storing the task metadata. The dataset can be created from the blade but the SQL Server and database need to already exist and be accessible to the data factory
•  If you want the pipelines to run on a specific runtime environment that needs to already exist
•  Source and destination connections can be created during the wizard steps, or you can use existing ones
• For this setup I was intending to customise it afterwards, so I just selected 2 tables to use as examples/templates 
•  I chose Configure for each table separately as the loading behaviour, and then Delta load for one table and left the other as Full load. For delta loading you need to select a Highwater column which will be used to track whether changes have occurred.
•  Allow the wizard to generate column mappings
• Also set the destination properties. We want to use Upsert for our Write behaviour so set this with the appropriate key. You can also add a Pre-copy script for example purposes and any other settings that you think might be useful later on

 Once all the selections have been made the wizard will create the ADF resources such as pipelines and datasets, as well as the SQL database tables and stored procs. 

From the ADF side this is 3 pipelines named xxx_TopLevel, xxx_MiddleLevel, xxx_BottomLevel. The top level pipeline is the main orchestrator and the one which will be scheduled to run. This calls the middle level, which then calls the bottom level one to do the actual export and destination population.

The heart of this SQL side is a table which contains a row for each entity that's being extracted, with columns of JSON values of the metadata that ADF interprets to run the pipelines.

Limitations and Next Steps

 I might be missing something, but updating the copy task for changes to existing items or adding new ones looks to be a bit of a chore. Seeing as I have time on my hands I'm going to try altering the generated objects to allow using some custom tables - sort of a metadata-driven metadata-driven task. More on this in the next post

 

 

SSIS ScriptComponent Outputs

 Something else for the "Stuff I Always Forget" category.

 There's a couple of tricks when using the SSIS Script Component as a data source.

Nulls

If one of the outputs is null you need to set the _IsNull property to true

(N)Varchar Max 

Max string columns need to be output using the Column.AddBlobData(System.Text.Encoding.Unicode.GetBytes(data)) format. 

Note that this needs to be Encoding.Unicode not Encoding.UTF8 otherwise you get the infamous odd byte number error  

DateTime 

DateTime output column data types should be of type database timestamp [DT_DBTIMESTAMP], even for datetime2 otherwise you get an overflow error

 

Example

ApiOutputBuffer.AddRow();

// Check nulls
                    if (string.IsNullOrEmpty(fullEndpoint))
                    {
                        ApiOutputBuffer.Endpoint_IsNull = true;
                    }
                    else
                    {
                        ApiOutputBuffer.Endpoint = fullEndpoint;
                    }

// Nvarchar max
                        ApiOutputBuffer.Response.AddBlobData(System.Text.Encoding.Unicode.GetBytes(content));
 

                    ApiOutputBuffer.ResponseDateTime = DateTime.Now; 

Carnivore Week 3 and Sum Up

 Week 3 was week 2 with dairy - mainly cream, butter and cheese. I realise this isn't strictly carnivore (seems to be referred to as dirty carnivore), so maybe more inline with zero carb or "animal based".

So how was it

This was the best week yet and I could comfortably eat like this the majority of the time. Everything felt pretty good, weight dropped another ½ kilo. The addition of butter for cooking made the meals much more enjoyable, and I had really missed cheese.

The Verdict and General Thoughts

My intention for doing this was to try and determine if there is anything to all the hype around this style of eating, or is it mainly a vehicle for getting social media credit. Putting it on here is just so I have a record of it in times to come (and I haven't done anything technically interesting of late).

The verdict - I think there's definitely something in it, and something I'll be looking to work in as a permanent change going forward.

 As with anything there are pros and cons. The biggest pros are health related - losing extra weight, better bowel function and feeling generally better. The cons were more "soft" cons, but still things that would need to be addressed to do this long term.,

Cons

Firstly, the rest of the family aren't keen on this style of eating, which means separate meal prep. Not a huge problem but a bit of a pain logistically having to cook multiple meals. Meal prep is, for me, another con. I enjoy cooking and experimenting with different foods and flavours.

I also like to eat a lot of the food that I gave up - though this is a problem with any diet change, and I suspect undoubtedly why many people fail to change their eating patterns.

These cons could probably be resolved pretty easily, e.g. having set days to eat and prepare more detailed meals, and if there're no real health issues having more of an "animal based" diet than strict carnivore.

Now for a short rant. 

It's also difficult to determine the long term effects. Trying to get information online swings from the "you'll die if you don't eat fruit and veg" to "fruit and vegetables are trying to kill you". Conventional medical and diet practitioners seem to be firmly in the first camp with most being very reluctant to hold a view outside of the 5+ a day and plenty of fibre mindset. The carnivore camp is getting almost as dogmatic in their views as the vegan community with an almost religious dedication to what can and can't be eaten, and digital crucifixion of anyone who decides they want to start incorporating any sort of plant. Ok, so probably only a few with this mindset, but it is (disappointingly) growing from what I've seen on social media since looking into this. It could also just be the algorithms trying to rage bait me, but does contribute to the difficulty of finding accurate information.

There's also the tendency for people to want to make money by selling you something - "vital" supplements, books/content to stop you making fatal mistakes etc. And man do some people go overboard - a simple statement to eat more fat results in people telling you to drink lard and add copious amounts of butter to your coffee, when I'm pretty sure the intent was to not cut the fat off your meat and maybe cook your eggs in some butter.

Rant over.

Other (Possible) Pros 

Other than the health benefits already mentioned, there were a handful of things that I noticed during the journey which I can't categorically say are related to the diet, but they do appear to be worth closer investigation. 

Smell/Odour - I try to limit the use of deodorant if not leaving the house, and must admit it can get a bit fragrant particularly in hot weather. This seemed to be significantly diminished by week 3.

Sun Tolerance -  I'm a pale specimen that rapidly turns pink in direct sunlight, but I avoid sunscreen where possible, preferring to cover up with clothing. We went for an impromptu walk during week 3 and although I had a hat, was only wearing short sleeves and the sun was pretty intense so I was expecting some arm heat and redness. This didn't eventuate as I thought it would, but maybe the sun UV wasn't as strong as I thought,

Skin itchiness - Occasionally my skin will have a very mild itch, which is noticeable but easy to ignore. This seemed to disappear in week 3.

So What Next?

As mentioned above, this had many benefits so I'm keen to work this into my regular eating. I'm thinking more of a zero carb, animal based diet, but with plant based herbs, spices and seasonings, for most of the week, then maybe a bit less rigid over the weekend to cater for some cooking enjoyment. Restricting treat foods to special occasions, which I had been doing but slipped a bit over Christmas. Hopefully this gives similar health outcomes while allowing me to scratch the mental itch around food preparation and enjoying different foods. Time will tell