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
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
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
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
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
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
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.
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