Thursday 11 February 2021

Extracting Data from CSV file to SQL Server using Azure Blob Storage and Azure Data Factory

Objective

One of the general ETL practice that is needed in the cloud is to inject data from a CSV file to a SQL the file will be within Azure Blob Storage and the destination SQL is a Azure SQL Server. Once moving to the cloud the the ETL concept changes because of the large amount of data or large amount of ETL file loads and for that a automated/configured mechanism is required to load the hundreds and hundreds of files maybe an IoT data load might be a good example, an IoT service may have have more than one hundred devices, each device generates hundreds of files every day.
The solution is to create a config table in SQL that contained all the information about an ETL like 
1 - Destination SQL table
2 - Mapping fields
3 - ...
4 - Source File Path
5 - Source File name and extension 
6 - Source File Delimiter
7 - etc...

Requirements

    The Azure Data Factory (ADF) will read the configuration table that contains the source and the destination information and finally process the ETL load from Azure Blob Storage (Container) to Azure SQL database, the most important part is that the config tale contains te mapping field in a JSON format in the config table


        A mapping JSON example looks like 
'
{"type": "TabularTranslator","mappings": [
{"source":{"name":"Prénom" ,"type":"String" ,"physicalType":"String"},"sink": { "name": "FirstName","type": "String","physicalType": "nvarchar" }},
{"source":{"name":"famille Nom" ,"type":"String" ,"physicalType":"String"},"sink": { "name": "LastName","type": "String","physicalType": "nvarchar" }},
{"source":{"name":"date de naissance" ,"type":"String" ,"physicalType":"String"},"sink": { "name": "DOB","type": "DateTime","physicalType": "date" }}
]}
'

Some of the config table are as mentioned
    Source Fields 
  1. [srcFolder] nvarchar(2000)
  2. [srcFileName] nvarchar(50)
  3. [srcFileExtension] nvarchar(50)
  4. [srcColumnDelimiter] nvarchar(10)
  5. etc...
    Destination Fields
  1. [desTableSchema] nvarchar(20)
  2. [desTableName] nvarchar(200)
  3. [desMappingFields] nvarchar(2000)
  4. ....
  5. [desPreExecute_usp] nvarchar(50)
  6. [desPostExecute_usp_Success] nvarchar(50)
  7. ....

Please note…
  • The DFT is called by a Master pipeline and has no custom internal logging system
  • The three custom SQL stored procedures are not included in this solution
  • Three custom SQL stored procedures can be used/set within the configuration table for each ETL , one for PreETL, second PostETLSuccess and Finally PostETLFailure
Do you want the code? Click here (From Google).

Extracting Data from SQL to CSV files using SSIS and PowerShell 7.x

 Objective

I decided to combine SSIS and PowerShell 7.0 to create one new ETL SSIS package that transfers CSV files to SQL server using a configuration table, the objective is to create a config table in SQL that contained all the information about an ETL like 
1 - Source SQL table
2 - Selected fields
3 - Filters like the t-sql WHERE clause 
4 - Tracking mechanism
5 - Destination File Path 
6 - Destination File name and extension
7 - etc...

.

Requirements

    The powershell script will read CSV files and insert the data into a SQL Server destiantion table, the parameters are as mentioned.
  1. [String]$srcSQLServerConnectionString = "Server = ABC ; Database = XYZ; Integrated Security = True;"
  2. [String]$srcSELECTQuery = "SELECT Fields  FROM tblXYZ"
  3. [String]$desFilePath = "<DRIVER>:\...\ETLFolder\FileToBeSFTPed"
  4. [String]$desFileName = "YYYY-MM-DD HHMMSS FileName"
  5. [String]$desFileExtension = "csv"                                 OR "txt"
  6. [String]$desColumnDelimiter = "TAB"                       # "TAB"   "`t"    |    ;    `
    At this point the SSIS package will read the config table, start a "Tracking mechanism" (getting the MAX identity field or MAX modified date) then create a final T-SQL string for the PowerShell.

            Some of the config table are as mentioned
    Source Fields 
  1. [srcTableSchema] nvarchar(20)
  2. [srcTableName] nvarchar(200)
  3. [srcFieldList] nvarchar(2000)
  4. [srcWHEREClause]                 nvarchar(1000)
  5. etc...
    Destination Fields
  1. [desFolder] nvarchar(2000)
  2. [desFileName] nvarchar(50)
  3. [desFileExtension] nvarchar(50)
  4. ....
  5. [srcPreExecute_usp] nvarchar(50)
  6. [srcPostExecute_usp_Success] nvarchar(50)
  7. ....

Please note…
  • PowerShell script may need to be registered
  • The SSIS has an internal logging system
  • Three custom SQL stored procedures can be used/set within the configuration table for each ETL , one for PreETL, second PostETLSuccess and Finally PostETLFailure
Do you want the code? Click here (From Google).

Saturday 30 March 2019

Leveraging IoT Device in Hydroelectric, Wind Power, Transformers, Heat Recovery & Power Stations and Azure Security Insight: Azure IOT Coast 2 Coast Tour - Regina, SK, Canada

“Azure IOT Coast 2 Coast” First Tour



Hi
Join me (Nik) and Deepak for two presentation focusing on …

1-   Leveraging IoT Device in Hydroelectric, Wind Power, Transformers, Heat Recovery & Power Stations.

Agenda / Topics

·       IoT Solutions
o   Azure IoT Central (SaaS)
o   Azure IoT Solution Accelerator (PaaS)
o   PaaS Services & IoT Services
·       Azure IoT Central Portal
·       Setting up a real IoT Device in to Azure IoT Central (demo).
·       Hydro/Power Transformers in Azure IoT central (demo).


2-   Azure security defenses you ought to know

Agenda / Topics

·       How Cloud Security is different & Better
·       Demo: Azure Advisor, Azure Security Center
·       Demo: Identity and access management
·       Advanced Threat Protection for your data


Venue: Sunrise Branch Library
  3130 Woodhams Dr, Regina, SK S4V 2P9
  Regina Saskatchewan CANADA

Time:               1 PM – 4 PM
Registration:   --
Requirement:  --
Price:               Free of cost
Parking:           Free of cost 
Please Note:    --

Sponsors:        --

Wednesday 27 March 2019

Azure IOT and Azure Defenses: Coast 2 Coast Tour

Azure IoT And Azure Defenses
Coast 2 Coast Tour


Join me and Deepak for ‘ Azure IOT and Azure Defenses: Coast 2 Coast Tour’ . 
We will be at
Saskatoon on May 3rd .
Join us for the great sessions on ‘IOT and Azure Coast to Coast’ at Saskatoon – Saskatchewan. Sessions will be held at Cliff Wright Branch Library on May 3rd to
“Learn IoT Device Translator & Azure Security you ought to know”.
Nik Shahriar from Toronto presenting at Calgary, Saskatoon and Regina.
Location : Cliff Wright Branch Library
Address: 1635 McKercher Dr, Saskatoon, SK S7H 5J9
Free Parking
TimePM – 8 PM
IoT Device Translator Nik Shahriar

Nik Shahriar 
  • Environmental settings (Visual Studio Code, Arduino, …)
  • IoT Hub translator architecture
  • IoT Hub, Speech Service, Azure Function.
  • Chinese to English, French to English, real demo
We will have Nik Shahriar from Toronto and he is presenting at Saskatoon and Regina.
Azure Security best Practices Deepak

Deepak
  • How Cloud Security is different & Better
  • Demo: Azure Advisor, Azure Security Center
  • Demo: Security Playbook & Identity and access management
  • Advanced Threat Protection for your data
We will serve Pizza, Snacks and Beverages

Also we will be at 
Calgary on April 27th.
Regina on May 4th  .

Thursday 14 February 2019

Azure Databricks, Excel custom functions programming, Azure Data Factory, Azure Logic Apps

Azure Databricks, Excel custom functions programming, Azure Data Factory, Azure Logic Apps




Please Join me and Niesh Sha and Heather Grandy for a presentation at Microsoft Toronto HQ.
Details
Welcome to C# Corner Toronto chapter meetup!

Join our February 2019 meetup and learn about Azure Logic Apps, Azure Data Factory, Azure Databricks and Excel custom functions programming.

-----------------------------------------------
Meetup starts sharply at 4.30 pm
-----------------------------------------------

Agenda:
• Introduction to C# Corner & it's Toronto chapter
• "Azure Databricks" by Heather Grandy
• "Excel custom functions programming" by Nilesh Shah
• Azure Data Factory and Azure Logic Apps Typical Samples (P1) by Nik Shahriar
• Refreshments, Discussion, Networking

Session details:

"Azure Databricks" by Heather Grandy
* Introduction to Spark
* Why Azure Databricks?
* Demo
-Navigating a Databricks workspace,
-Creating a cluster,
-The Notebook development experience

"Excel custom functions programming" by Nilesh Shah
* What are custom functions in Excel
* Setup & Requirements
* Streaming Custom Functions
* Demo

Azure Data Factory and Azure Logic Apps Typical Samples (P1) by Nik Shahriar
* File processing and Archiving
* Unzipping files using ADF, ALA and Python
* Pipeline Framework
* Demo

Speakers' Introduction:
• Nilesh Shah - Nilesh is Microsoft MVP in Office 365 development. He is working as Sr. Tech Lead at RN Design Ltd.

• Heather Grandy - Heather is Azure Data Platform Technical Specialist at Microsoft Canada in Toronto.

• Nik Shahriar - Nik is Snr Azure Data Engineer/Snr Azure Integration Design/Snr Tech. Team Lead & Design. He is C# Corner MVP and former Microsoft MVP

Sponsor:
C# Corner Toronto chapter is sponsored by RN Design Ltd.

C# Corner Toronto chapter thanks Microsoft Canada for the venue!


Monday 28 January 2019

Hand-On Azure Data Factory

Hand-On Azure Data Factory




        Please join me (Nik- Shahriar Nikkhah) on a new webinar with "Azure Data Factory”. 
Thursday, Jan 31, 2019 12:00 PM – 01:00 PM EST (Toronto, Canada Time
Price: Free of cost 
Note: There are 250 seats only. First come first serve. 
Introducing Azure Data Factory (HAND-ON Session-Demo)
  • What is Azure Data Factory 
  • Design pipeline Framework 
  • Design ADF Framework 
  • Master Pipelines
Azure Data Factory
12:15 PM – 01:00 PM           EST
 Session details are as follows, 
Webinar: Hand-On session on Azure Data Factory 
Thur, Jan 31, 2019 12:00 PM – 01:00 PM EST 
Please join my meeting from your computer, tablet or smartphone. 
You can also dial in using your phone. 
Access Code: Must register 
Registration URL:
https://register.gotowebinar.com/register/2782111160902085387

Webinar ID:
992-728-619
First GoToMeeting? Let’s do a quick system check:

Thursday 24 January 2019

Azure Security Defenses you ought to know AND Hand-On session on Azure Data Factory

Azure Security Defenses you ought to know AND Hand-On session on Azure Data Factory


        Please join me (Nik- Shahriar Nikkhah) and Deepack Kaushik on new webinar on “Azure Security Strategies you ought to know and Introducing Azure Data Factory”. 
Sat, Jan 26, 2019 8:00 AM – 10:00 PM CST (Saskatchewan Time
Price: Free of cost 
Note: There are 250 seats only. First come first serve. 
Agenda:Azure Security Defenses you ought to know 
  • Why Cloud Security is different & better.
  • Azure Security Center
  • Confidentiality. Integrity. Availability. (CIA)
  • Advanced Threat Protection: for your data
Introducing Azure Data Factory (HAND-ON Session-Demo)
  • What is Azure Data Factory (ADF)?
  • Looking at ADF from a Farm perspective.
  • How to build Pipeline Framework
  • How to build ADF Framework
  • Dynamic ADF Pipeline
  • ADF and External Services (ALA)
Azure Security Strategies you ought to knowDeepak Kaushik08:00 AM – 09:00 AM
Introducing Azure Data FactoryShahriar Nikkhah09:00 AM – 10:00 AM
 Session details are as follows, 
Webinar: Azure Security Defenses you ought to know AND Hand-On session on Azure Data Factory 
Sat, Jan 26, 2019 8:00 AM – 10:00 AM PST 
Please join my meeting from your computer, tablet or smartphone. 
https://global.gotomeeting.com/join/144831997
You can also dial in using your phone. 
Canada: +1 (647) 497-9391
Access Code: 144-831-997 
First GoToMeeting? Let’s do a quick system check:
https://link.gotomeeting.com/system-check