Azure SQL Database - ports you didn't realize you needed for your firewall request.

 Azure is fun. FUN, I say!

Ports: 

1433 - standard SQL port

11000-11999 - ports if you're using Proxy instead of Redirect

14000-14999 (!) - ports for the DAC (Dedicated Admin Connection)

Mermaid (the language) is life!

Just found out about this the past month. 

I like diagrams for my documentation, and I detest making it. I also would like to build it via script, since that's more useful.

Sample:

graph TD;

     A-->B;

     A-->C;

     B-->D;

     C-->D;

Which produces: 

Pretty cool right? 

VSCode supports it (load the extension "Markdown Preview Enhanced"), but Jira and Confluence don't support it natively.... 

But there's a workaround!

In Chrome, load the extension Mermaid Previewer (https://chrome.google.com/webstore/detail/mermaid-previewer/oidjnlhbegipkcklbdfnbkikplpghfdl)

There are a couple of tweaks. For one, you need to enable sandbox mode in the extension. 

For Confluence, I put the site as my\.confluence\.site and a selector of pre > code. To add it, I create a markdown block, but within it do this:

```mermaid

graph LR

A --> B

```

(update 2023/10/27) another way that seems to work for confluence is to make a regular code block ( for me, type ``` by hand, then do it with just the "graph LR / A --> B"), and in Mermaid Previewer as div.container, but I don't know how that affects other "containers". the nice thing about doing it the other way is that (for some reason) you HAVE to have the ```mermaid, which means it can't accidentally go crazy with it.

For Jira, my selector is pre.code-java, since by default our code blocks are Java. You can look at the element in Chrome developer mode (right-click, choose "inspect").

Then, within the Jira ticket, just do:

graph LR

A --> B 

(NOTE THE SPACES!) No idea why it doesn't act the same between them, but for me it's fine.

SSIS and Parameters and where to set them

 SSIS has a LOT of ways to set configs. Parameters & variables, and then those can be overridden at different levels.

in order (from lowest to highest) - bottom of this list supercedes the top. You can see WHERE it was overridden because at that level, the parameter name is in bold. 

1. SSIS Package level
2. SSIS Project level
3. XML Configs? (I don't remember where these can be set)
4. SSISDB - right-click on the project within integration catalog and choose "Config"
5. SSIS Job Step configuration

Azure Data Factory - how to ACTUALLY use storage event triggers to copy data from Blob Storage to Azure SQL Database (Azure SQL DB)

 This post is in response to an incredibly frustrating interaction with Azure.

I figured I would do something incredibly simple - import a parquet file from blob storage into Azure SQL DB. When the file is written to Blob Storage (using CETAS, fwiw), it should activate the storage trigger, which should kick off the pipeline. 

The problem is that it won't work as the tutorial (https://learn.microsoft.com/en-us/azure/data-factory/tutorial-copy-data-portal) shows. It says it can't find the file. Why not? Well, it wasn't firewalls or anything else. It's that a linked service dataset, in "Sink" requires a container name - and the trigger sends the container name also. So I kept getting messages that it couldn't find \mycontainer\mycontainer\myfolder\myfile.parquet . I found a single message on Stack Overflow (https://stackoverflow.com/questions/67294521/azure-data-factory-how-to-get-new-file-location-from-triggerbody-folderpath/74738786) on how to solve it. So, here we go: 

package parameters:

Source:

@pipeline().parameters.trigger_folder_path

@pipeline().parameters.trigger_file_name

My Source Dataset:

Sink:

And the last part in the trigger that makes it all work:

Here's what to put for trigger_folder_path, as per Steve Johnson on SO. No, you don't have to change the YAML, you can modify the values above: 

@substring(triggerBody().folderPath,add(indexof(triggerBody().folderPath,'/'),1),sub(length(triggerBody().folderPath),add(indexof(triggerBody().folderPath,'/'),1)))

Why it's that hard to find/use, I have no idea. 

All Hail Steve Johnson!