1 / 26

Creating a Meta Data Driven SSIS Solution with Biml

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: marco@in2bi.nl Comment: http://blog.in2bi.com. Biml.

ajaxe
Download Presentation

Creating a Meta Data Driven SSIS Solution with Biml

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Creating a Meta Data Driven SSIS Solution with Biml Marco Schreuder

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

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

  4. Solution Creating a Meta Data Driven SSIS Solution with Biml

  5. The Boss Creating a Meta Data Driven SSIS Solution with Biml

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

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

  8. SB01 Simple Package Creating a Meta Data Driven SSIS Solution with Biml

  9. SB02 Simple packages Creating a Meta Data Driven SSIS Solution with Biml

  10. SB02 How it works xml xml Creating a Meta Data Driven SSIS Solution with Biml

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

  12. SB03 Modular Packages Control Flow Data Flow Creating a Meta Data Driven SSIS Solution with Biml

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

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

  15. SB04 Packages with Logging Control Flow Data Flow Creating a Meta Data Driven SSIS Solution with Biml

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

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

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

  19. SB05 Logging errors / TASK Creating a Meta Data Driven SSIS Solution with Biml

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

  21. I discussed dimensions with the business Creating a Meta Data Driven SSIS Solution with Biml

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

  23. SCD Wizard? ... I thought that didn’t perform? Creating a Meta Data Driven SSIS Solution with Biml

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

  25. SB08 Master package Creating a Meta Data Driven SSIS Solution with Biml

  26. THank you Goal: Get them interested in Biml to start using it So please: Tweet: @in2bi Mail: marco@in2bi.nl Comment: http://blog.in2bi.com Creating a Meta Data Driven SSIS Solution with Biml

More Related