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

Oops…I Broke the SQL Server

So this happened.  In an attempt to give my SQL Server Instance access to more memory, I set the Max Memory to zero, expecting that to mean infinite.  No luck, SQL Server Management Studio (SSMS) set the max memory to 16MB instead and broke the instance.  I could not do anything more in SSMS because … Continue reading Oops…I Broke the SQL Server

Enabling the Unknown Member on a Dimension in SSAS.

Do you ever get this error: Warning 5 Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: '', Column: '', Value: '12:00:00 AM'. The attribute is 'Date'. It turns out that this error occurs because: Null values in the fact table relationship are converted to '12:00 A.M.' So I … Continue reading Enabling the Unknown Member on a Dimension in SSAS.

SQL Server 2014 Installation

Last week I upgraded our Development box to SQL Server 2014.  Since this was a clean Dev box I had the choice of upgrading from SQL Server 2012 or doing a remove-install.  I figured that rather than leave all the detritus of the prior installs and versions of Data Tools, I would do a remove-install. … Continue reading SQL Server 2014 Installation