Creating Azure Storage SAS Tokens with ARM Templates - USEFUL LINKS!

 https://blog.kloud.com.au/2018/11/07/creating-azure-storage-sas-tokens-with-arm-templates/

https://samcogan.com/generate-sas-tokens-in-arm-teamplates/

Azure PowerShell to automate security setup for Azure API Management REST API

Looks like I haven't blogged here in quite a while! 

Hello there! 

Now I am back with the new stuff - I have developed an intense interest in Azure. 

So I will be adding a few blog posts related to my happy moments with Azure. 

In this post I am describing how to setup security required for the use of Azure Management REST API. I want to be able to start my Data Factory from my on-premises orchestration tool, and I am using Azure API Management REST APIs to do that. 

First of all, I need to set the security with the Security Principal in Azure to be able to login to Azure REST API with client secret. 

 See below the script that 

1.  Installs AzureAD module and connects to the tenant 

   
   Install-Module AzureAD
   

   
   Connect-AzureAD -TenantId "00000-0000-0000-000-0000"
   

2. Registers application in Azure AD 

   
   $subscriptionId = "00000-0000-0000-000-0000"
   $resourceGroup = "RG"
   $factory = "DF"
   $keyVaultName = "KV"
   
   $appName = "appreg"
   
   $appReg = New-AzureADApplication -DisplayName $appName -AvailableToOtherTenants $false
   
   $appReg
   

3. Creates a Service Principal for this application 

   
   New-AzADServicePrincipal -ApplicationId $appReg.AppId -Scope "/subscriptions/$subscriptionId/resourceGroups/$resourceGroup/providers/Microsoft.DataFactory/factories/$factory"
   

4. Creates a new client secret 

   
   $startDate = Get-Date
   $endDate = $startDate.AddYears(100)
   $aadAppsecret = New-AzureADApplicationPasswordCredential -ObjectId $appReg.ObjectId -CustomKeyIdentifier "SecretFactoryContributor" -StartDate $startDate -EndDate $endDate
   

5. Saves this new Client Secret and Client Id in the Key Vault for the use in Azure Management REST APIs

   
   $secret_value = $aadAppsecret.Value
   
   $Secret = ConvertTo-SecureString -String $secret_value -AsPlainText -Force
   Set-AzKeyVaultSecret -VaultName $keyVaultName -Name 'FactoryContributorClientSecret' -ContentType "Client Secret used to get token for Application $appName " -SecretValue $Secret
   
   $secret_value = $appReg.AppId
   
   $Secret = ConvertTo-SecureString -String $secret_value -AsPlainText -Force
   Set-AzKeyVaultSecret -VaultName $keyVaultName -Name 'FactoryContributorClientId' -ContentType "Client Id used to get token for Application $appName " -SecretValue $Secret
   
   

Having this script ready will save me some time when setting up new Azure environments and ensure that I have same settings everywhere. I am not a fan of manual setups!

Data Profiling Task for Multiple Tables

I am a big fan of BIML, so when I was faced with the task of creating DataProfile Tasks for several dozens of tables in the database, I naturally turned to a quick and easy BIML solution:

There are however some other alternative solutions, like here: Using the Data Profiling Task To Profile All the Tables in a Database

Remember That The Regex Engine Is Eager

Just a reminder:

Regex is eager and will return the first match in the piped list expressions: (<= |<>|<)

that was important for me for trying to match equality signs, for example: "<>" or "<=" vs "<".

https://www.regular-expressions.info/alternation.html

Developing Custom SSIS Data Flow Component: helpful links

Great resource to start with. A sample source code is also available for download.
http://geekswithblogs.net/Compudicted/archive/2012/03/01/creating-a-custom-ssis-data-flow-component---an-example.aspx

How to work with Connections
https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-custom-objects-data-flow-types/developing-a-custom-source-component

Debugging Custom component
https://docs.microsoft.com/en-us/sql/integration-services/extending-packages-custom-objects/building-deploying-and-debugging-custom-objects

1. Component Project in DEBUG mode
2. register .dll in GAC and copy it in the DTS\PipelineComponents
3. in SSIS project: breakpoint on Pre-Execute of the Data Flow Task. Wait till we hit breakpoint
4. in Component project - Debug - > Attach to process. Select DtsDebugHost.exe (Managed, x64)
5. in Component project - put a breakpoint in code in Validate or PreExecute or ProcessInput
6. in SSIS project: click Continue
7. you should hit the breakpoint in the Component project

Automate with Post-build event: registering .dll in GAC and copying it in the DTS\PipelineComponents
http://toddmcdermid.blogspot.ca/2009/01/custom-component-developing-post-build.html

this worked:
copy "$(TargetDir)$(TargetName).DLL" "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\PipelineComponents\"
gacutil.exe -i "$(TargetDir)$(TargetName).DLL"

Supporting different SQL Server platforms:

Support multi-targeting in your custom components

Getting your SSIS custom extensions to be supported by the multi-version support of SSDT 2015 for SQL Server 2016

Using PerformUpgrade

https://www.red-gate.com/simple-talk/sql/ssis/developing-a-custom-ssis-source-component/

https://blogs.perficient.com/microsoft/2016/04/developing-custom-ssis-component-9-lessons-learned/

old but good - MSIL Disassembler tool

Thanks to a pesky issue with a custom icon in the Custom SSIS Data Flow Component, I found out how useful MSIL Disassembler tool could be!
http://www.sqlis.com/sqlis/post/How-to-add-an-icon-to-your-component.aspx

GAC and debugging

Just a quick note: NEVER deploy your class library in the GAC on your DEV machine - debugging becomes hell, because Visual Studio 2015 referes to the class library in the GAC instead of the one in your Solution.

This is not too easy if you need to use GAC in order to refer to such class in your SSIS Script Component!

So now I have to uninstall it from GAC every time I need to debug