Here is the full document that I referred to in the Seminar. (Updated 8/5/2019) Moving Data to the Cloud with Azure Data FactoryDownload I've also added a login so you can see the objects I worked on in the seminar.User Id: student@omwtm.comPassword: JB_s7K%(et;Y Let me know if you have any questions or comments.
ETL
When Loading Data, Should I Drop Indexes or Not?
I just ran a few simple tests in Azure SQL DB to see how each would perform. I have a target table with an identity column that is the clustered primary key, and two other indexes that are the same except for the field order. (Whether having both is useful is a question for another … Continue reading When Loading Data, Should I Drop Indexes or Not?
Connecting to IBM DB2 zOS from Azure Data Factory v2
Connecting to IBM DB2 zOS from Azure Data Factory v1 was a matter of setting up the Azure Data Gateway on an on-prem server that had the IBM DB2 Client installed; creating an ODBC connection to DB2 (I called it DB2Test). Then, in the Data Factory v1 Copy Wizard, Select the ODBC source, pick the … Continue reading Connecting to IBM DB2 zOS from Azure Data Factory v2
Biml Tutorial Part 3 – Adding Connection Managers to our Package Generator
Now that we can generate simple empty packages, the next step is to add connection managers to the project and package. After that, we will add a DataFlow. This article will focus on the Connections though, and we will start with a pair of standard OleDb Connections. Recall the metadata tables from the last article: … Continue reading Biml Tutorial Part 3 – Adding Connection Managers to our Package Generator
Biml Tutorial 2 – Creating a Simple Package Generator
So I spent yesterday morning entering the metadata to describe some 70 or so packages that are all part of a big data load that moves data off the mainframe (DB2) into SQL Azure. Occasionally, I would build the packages with BIML Express to ensure I hadn't introduced bad metadata. Today, I tweaked the BIML to allow for … Continue reading Biml Tutorial 2 – Creating a Simple Package Generator
Biml Tutorial 1 – Introducing BIML
Intro to Automated Package and Template Generation with BIML In order to simplify and automate the template creation and package creation process, we will create a Proof-of-concept (POC) that uses the Business Intelligence Markup Language (BIML, pronounced bim-ul). BIML provides a language with which to generate SSIS packages using highly configurable metadata. Business Intelligence Markup … Continue reading Biml Tutorial 1 – Introducing BIML
The Common ETL Framework Part 2
Auditing/Logging Each package is responsible for logging at two different levels: Audit and Debug. The Audit level of logging needs to record the following for each package execution: Start Day and Time Executing Machine name Executing as Username Records Affected (if appropriate) – Can include Records Inserted, Updated, Deleted, Archived, Errored, etc. Error data if … Continue reading The Common ETL Framework Part 2
The Common ETL Framework Part 1
Defining a Common ETL Framework SQL Server Integration Services (SSIS) provides a mechanism to Extract data from some source, Transform it as necessary, and Load it into the tables in a target database (ETL). Each SSIS package should implement a common framework for logging, executing, monitoring and error handling so that these details are abstracted … Continue reading The Common ETL Framework Part 1
One Man went to Mow... 