Creating a meta data driven ssis solution with biml
Download
1 / 26

Creating a Meta Data Driven SSIS Solution with Biml - PowerPoint PPT Presentation


  • 281 Views
  • Uploaded on

Creating a Meta Data Driven SSIS Solution with Biml. Marco Schreuder. Who I am. Name: Marco Schreuder Nationality: Dutch Company: in2bi Datawarehousing sql server SSIS SSAS SSRS (limit) Tweet: @in2bi Mail: [email protected] Comment: http://blog.in2bi.com. Biml.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Creating a Meta Data Driven SSIS Solution with Biml' - ajaxe


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

Who i am
Who I am

Name: Marco Schreuder

Nationality: Dutch

Company: in2bi

  • Datawarehousing sql server

  • SSIS

  • SSAS

  • SSRS (limit)

    Tweet: @in2bi

    Mail: [email protected]

    Comment: http://blog.in2bi.com

Creating a Meta Data Driven SSIS Solution with Biml


Biml

  • Business Intelligence Markup Language

  • Invention of Varigence (varigence.com)

  • (Partly) donated to the (open source)BIDS helper projectbidshelper.codeplex.com

  • Describes BI Solution in a simple xml format

  • Biml script to automate package creation

Creating a Meta Data Driven SSIS Solution with Biml


Solution
Solution

Creating a Meta Data Driven SSIS Solution with Biml


The boss
The Boss

Creating a Meta Data Driven SSIS Solution with Biml


Meta data
Meta data

  • Sql

    • Sys.

  • dtsx xml file describing

    • Control Flow

    • Data Flow

    • Lay-out in BIDS

  • .biml

Creating a Meta Data Driven SSIS Solution with Biml


Solution1
Solution

source

staging

dwh

BIML

META DATABASE

Creating a Meta Data Driven SSIS Solution with Biml


Sb01 simple package
SB01 Simple Package

Creating a Meta Data Driven SSIS Solution with Biml


Sb02 simple package s
SB02 Simple packages

Creating a Meta Data Driven SSIS Solution with Biml


Sb02 how it works
SB02 How it works

xml

xml

Creating a Meta Data Driven SSIS Solution with Biml


GREAT!! ... But ...

  • Shouldn’t you TRUNCATE the destination ...

  • And what if bulk inserts fails?

You better take a MODULAR approach

Creating a Meta Data Driven SSIS Solution with Biml


Sb03 modular packages
SB03 Modular Packages

Control Flow

Data Flow

Creating a Meta Data Driven SSIS Solution with Biml


Sb03 what changed
SB03 What CHanged

  • <#@ include file="SB00_Connections.biml" #>that Is used to import another biml file (or part) to optimise reuse

  • We changes the start and end position of the for each loop Making it possible to create more than one file

  • Next we added a condition to check if we should truncate the destination filea setting in the meta data table

  • We added two extra destinations to the data flow and used the error path as input path(explain: default = name.output but there are others like name.error and later we’ll see some more)

  • Of course we have to define errorhandling in the destination components<ErrorHandlingErrorRowDisposition="RedirectRow" TruncationRowDisposition="RedirectRow" />

Creating a Meta Data Driven SSIS Solution with Biml


WOW!! ... But ...

  • Shouldn’t we do some logging and execution lineage?

  • Please log:

  • # rows in destination before and after

  • # rows inserted

  • # errorrows (in errorfile)

  • Start- and EndDate

Creating a Meta Data Driven SSIS Solution with Biml


Sb04 packages with logging
SB04 Packages with Logging

Control Flow

Data Flow

Creating a Meta Data Driven SSIS Solution with Biml


Sb04 logging
SB04 Logging

  • SQL: Audit Table

  • SQL: 2 Stored Procedures

    • uspNewAuditRow

    • uspupdateAuditRow

  • BIML: Add Variables

  • BIML: 2 Execute SQL Tasks (start and end)

  • Counters:

  • BIML: 2 Execute SQL Tasks (getting #rows)

  • BIML: 2 Row Count components in dataflow

Creating a Meta Data Driven SSIS Solution with Biml


Almost there ...

  • Let’s discuss:

  • Configurations

  • Logging error messages

  • Logging execution of tasks

  • Should they be included in our ETL framework?

Creating a Meta Data Driven SSIS Solution with Biml


Sb05 configurations
SB05 Configurations

<PackageConfigurations>

<PackageConfiguration Name="META" >

<EnvironmentVariableInput EnvironmentVariable="Northwind_Config“ />

<ConfigurationValues>

<ConfigurationValue DataType=“String"

PropertyPath="\Package.Connections[META].Properties[ConnectionString]"

Name="META"

Value="Data Source=.;Initial Catalog=MyDwh_meta;Provider=SQLNCLI10.1;Integrated Security=SSPI;">

</ConfigurationValue>

</ConfigurationValues>

</PackageConfiguration>

<PackageConfiguration Name="<#=pkg["SourceConnection"]#>" ConnectionName="META">

<ExternalTableInput Table="[dbo].[SsisConfiguration]" />

</PackageConfiguration>

<#if (pkg["SourceConnection"].ToString()!=pkg["DestinationConnection"].ToString()) {#>

<PackageConfiguration Name="<#=pkg["DestinationConnection"]#>" ConnectionName="META">

<ExternalTableInput Table="[dbo].[SsisConfiguration]" />

</PackageConfiguration>

<#}#>

</PackageConfigurations>

Creating a Meta Data Driven SSIS Solution with Biml


Sb05 logging errors task
SB05 Logging errors / TASK

Creating a Meta Data Driven SSIS Solution with Biml


Intermezzo qa
InterMEZZo / QA

  • We discussed:

  • Need for automation in datawarehousing

  • Biml – how it works

  • Bimlscript

  • ETL Framework

  • Responding to changes with Biml

  • Next:

  • Dimension table loading

  • Factable loading

  • Masterpackage

Creating a Meta Data Driven SSIS Solution with Biml


I discussed dimensions with the business

Creating a Meta Data Driven SSIS Solution with Biml


Sb06 dimension packages
SB06 Dimension packages

We use views to join staging tables

An extra table in the meta database with column information

SCD Transformation (Wizard)

Creating a Meta Data Driven SSIS Solution with Biml


SCD Wizard? ...

I thought that didn’t perform?

Creating a Meta Data Driven SSIS Solution with Biml


Sb07 facttable packages
SB07 FactTable packages

We use a view to join staging tables

An extra table in the meta databse with column information

Lookup component to lookup keys of dimension tables

Creating a Meta Data Driven SSIS Solution with Biml


Sb08 master package
SB08 Master package

Creating a Meta Data Driven SSIS Solution with Biml


Thank you
THank you

Goal: Get them interested in Biml to start using it

So please:

Tweet: @in2bi

Mail: [email protected]

Comment: http://blog.in2bi.com

Creating a Meta Data Driven SSIS Solution with Biml


ad