CREATING A META DATA DRIVEN SSIS SOLUTION WITH BIML MARCO SCHREUDER

CREATING A META DATA DRIVEN SSIS SOLUTION WITH BIML MARCO SCHREUDER

WHO I AM Name: Marco Schreuder Nationality: Dutch Company: in2bi Tweet: Datawarehousing sql server SSIS SSAS SSRS (limit) @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 project bidshelper.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 Creating a Meta Data Driven SSIS Solution with Biml

THE BOSS Creating a Meta Data Driven SSIS Solution with Biml

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

SOLUTION source staging BIML META DATABASE Creating a Meta Data Driven SSIS Solution with Biml dwh

SB01 SIMPLE PACKAGE Creating a Meta Data Driven SSIS Solution with Biml

SB02 SIMPLE PACKAGES Creating a Meta Data Driven SSIS Solution with Biml

SB02 HOW IT WORKS BIML SCRIPT EXPAND xml Creating a Meta Data Driven SSIS Solution with Biml xml GENERATE

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 Control Flow Data Flow Creating a Meta Data Driven SSIS Solution with Biml

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 file a 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 ErrorHandling ErrorRowDisposition "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 Control Flow Creating a Meta Data Driven SSIS Solution with Biml Data Flow

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 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 Errors Task Events: OnError OnPreExecute OnPostExecute Table SsisErrorLog SsisTaskLog Stored Procedures uspNewErrorLogRow uspNewTaskLogRow uspUpdateTaskLogRow Creating a Meta Data Driven SSIS Solution with Biml

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 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 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 Creating a Meta Data Driven SSIS Solution with Biml

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