biml ezapi n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
BIML & EzAPI PowerPoint Presentation
Download Presentation
BIML & EzAPI

Loading in 2 Seconds...

play fullscreen
1 / 39

BIML & EzAPI - PowerPoint PPT Presentation


  • 531 Views
  • Uploaded on

2013-09-05. BIML & EzAPI. BIML and EzAPI. Two approaches to creating SSIS packages programmatically Daniel Otykier // do@kapacity.dk. Agenda. About me Why SSIS programmatically ? Prerequisites BIML Introduction Samples References EzAPI Introduction Samples References

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 'BIML & EzAPI' - leon


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
biml and ezapi

BIML and EzAPI

Two approaches to creating SSIS packages programmatically

Daniel Otykier // do@kapacity.dk

agenda
Agenda
  • Aboutme
  • Why SSIS programmatically?
  • Prerequisites
  • BIML
    • Introduction
    • Samples
    • References
  • EzAPI
    • Introduction
    • Samples
    • References
  • Summary and discussion
    • Strength and weaknesses
    • Comparing BIML and EzAPI

Feel free to ask questions along the way!

about me
Aboutme
  • Daniel Otykier do@kapacity.dk
  • 28 years old, married, no kids (yet)
  • Cand. IT.
  • 5 years of experience with SQL Server and Microsoft BI
  • 8+ years of experience with C# and .NET
  • BI consultant and developer at Jyllands-Posten since August 2008
  • Senior Business Intelligence Consultant at KapacitysinceFebruary 2013
why work with ssis programmatically
Why work with SSIS programmatically?
  • Eliminate repetitive and time-consuming labor
    • 1:1 data extracts
    • Controller packages
    • Dimension Load packages
  • Create metadata-driven SSIS solutions
    • Connections, source tables, columns, transformations = metadata
    • No manual editing SSIS packages when things change
    • Improve manageability of larger solutions
  • Increase productivity
    • Reuse existing work
    • Use templates

…and for the code-loving programmers among us:

  • Work with structured code rather than a clumsy GUI
prerequisites
Prerequisites?
  • Developer
    • Solid SSIS experience
  • Software
    • SQL Server 2008R2 or 2012
    • BIDS or SSDT
    • For BIML:
      • BIDS Helper - http://bidshelper.codeplex.com/
    • For EzAPI:
      • Visual Studio edition with C# or VB.NET Project Type
      • EzAPI.dll - http://sqlsrvintegrationsrv.codeplex.com
biml introduction
BIML introduction
  • Developed and supported by Varigence
  • XML-based
  • Abstract description of BI solution
  • ASP.NET-style scripting (BIMLScript)
  • Free version with BIDS Helper
    • Generate SSIS packages
    • Integrates nicely in SSIS projects
  • Full version with Mist™ IDE (Varigence)
    • Generate SQL tables, SSAS dimensions, cubes, etc.
using biml
Using BIML
  • Make sure BIDS Helper is installed!
    • Create .biml files
    • Check for errors
    • Execute BIML Script = “Generate SSIS packages”
visual studio hints
Visual Studio hints
  • biml.xsd for syntax highlighting / IntelliSense
  • Disable xml formatting on paste:
biml syntax
BIML syntax

Creating a package that contains a single data flow task:

<Bimlxmlns="http://schemas.varigence.com/biml.xsd">

<Packages>

<PackageName="MyTestPackage"ConstraintMode="Linear">

<Tasks>

<DataflowName="My Data Flow">

<!-- ...dataflow components here... -->

</Dataflow>

</Tasks>

</Package>

</Packages>

</Biml>

biml xml tags
BIML XML tags

Many BIML XML tags, corresponding to the various SSIS tasks, components, etc. Examples:

  • <Packages>
  • <Package>
  • <Tasks>
  • <ExecuteSQL>
  • <ExecutePackage>
  • <FileSystem>
  • <Ftp>
  • <SendMail>
  • <ForEachFileLoop>
  • <Script>
  • <Container>
  • <DataFlow>
  • <Transformations>
  • <OleDbSource>
  • <ExcelSource>
  • <DerivedColumns>
  • <Sort>
  • <Merge>
  • <DataConversion>
  • <AdoNetDestination>
  • <Connections>
  • <Variables>
  • <PackageConfigurations>

…and many, MANY more!

bimlscript basics
BIMLScript basics
  • Inline C# or VB.NET scripting with <# and #>
    • Same as T4 templates
  • Example: C# variables

<#@templatelanguage="C#"hostspecific="true"#>

<#varpackageName= "TestPackage";#>

<Bimlxmlns="http://schemas.varigence.com/biml.xsd">

<Packages>

<PackageName="<#=packageName#>" ConstraintMode="Linear"/>

</Packages>

</Biml>

bimlscript basics1
BIMLScript basics
  • Example: C# conditionals

<#@templatelanguage="C#"hostspecific="true"#>

<#varvalue = 1;#>

<Bimlxmlns="http://schemas.varigence.com/biml.xsd">

<Packages>

<#if(value == 1){#>

<PackageName="PackageA"ConstraintMode="Linear"/>

<#}else{#>

<PackageName="PackageB"ConstraintMode="Linear"/>

<#}#>

</Packages>

</Biml>

bimlscript basics2
BIMLScript basics
  • Example: C# loops

<#@templatelanguage="C#"hostspecific="true"#>

<Bimlxmlns="http://schemas.varigence.com/biml.xsd">

<Packages>

<#for(vari = 1;i <= 5;i++){#>

<PackageName="Package <#=i#>" ConstraintMode="Linear"/>

<#}#>

</Packages>

</Biml>

  • When running this BIML, it is expanded into…
bimlscript loop expansion
BIMLScript loop expansion

<Bimlxmlns="http://schemas.varigence.com/biml.xsd">

<Packages>

<PackageName="Package 1"ConstraintMode="Linear"/>

<PackageName="Package 2"ConstraintMode="Linear"/>

<PackageName="Package 3"ConstraintMode="Linear"/>

<PackageName="Package 4"ConstraintMode="Linear"/>

<PackageName="Package 5"ConstraintMode="Linear"/>

</Packages>

</Biml>

  • All inline C# code is gone!
  • …and then SSIS packages are generated.
using metadata
Using metadata

<#@templatelanguage="C#"hostspecific="true"#>

<Bimlxmlns="http://schemas.varigence.com/biml.xsd">

<Packages>

<#

varmetadataConnection = "Provider=SQLNCLI11;Server=localhost;Initial Catalog...

varmetadataSql = "SELECT PackageName FROM Packages";

varmetadataTable = ExternalDataAccess.GetDataTable(metadataConnection,

metadataSql);

foreach(DataRowrowinmetadataTable.Rows)

{#>

<PackageName="<#=row["PackageName"]#>" ConstraintMode="Linear">

</Package>

<#} #>

</Packages>

</Biml>

Repeated for every row in table “Packages”

real world sample
Real-world sample
  • Generate all extract packages from metadata!
  • Metadata table containing:
    • Connection Manager name (also used as Schema name for extract tables)
    • Source table name or SQL SELECT-statement
    • Destination table name
    • Optional pre-execute and post-execute SQL statements (for dropping and creating indices, etc.)
  • BIML script generates:
    • Extract package for each row in metadata table
    • Controller package, that executes every extract package
general usage of metadata with biml
General usage of metadata with BIML
  • Metadata source options:
    • INFORMATION_SCHEMA.TABLES
    • Custom metadata table (previous slide)
    • Custom XML or text file metadata
  • BIML automatically maps dataflow columns if
    • Column names match in source and destination
    • Column mappings are explicitly specified in BIML
  • What happens when metadata is changed?
    • Just execute BIML script again
    • Don’t edit generated SSIS-packages manually
more biml
More BIML
  • BIML supports most SSIS features:
    • Variables
    • Expressions
    • Configurations
    • Events
more biml1
More BIML
  • BIML supports new SSIS 2012 features:
    • Project Connection Managers:<OleDbConnectionName="OLTP" ConnectionString=”...”CreateInProject="true"/>
    • Package Parameters:<Parameter DataType="String" Name=”MyParam">MyValue</Parameters>
    • Useproject/package parameters just like variables:<Variable Name=”MyVariable" DataType="String”EvaluateAsExpression="true">@[$Package::MyParam]</Variable>
biml references
BIML references

BIML has an active user community:

  • Varigence.comhttp://www.varigence.com/Documentation/Samples/Biml
  • BIMLScript.comhttp://www.bimlscript.com/Browse/Snippets
  • bidshelper.codeplex.com:http://bidshelper.codeplex.com/wikipage?title=Samples%20and%20Tutorials
ezapi introduction
EzAPI introduction
  • Developed by the Microsoft SSIS team
  • Written in C#, source code available
  • Wraps low-level DTS libraries
  • Use with any .NET compatible language (C#, VB.NET, Visual C++, etc.)
  • Maximum flexibility
  • Everything that can be done in BIDS, can be done with EzAPI
  • “Scripttime” metadata access
terminology
Terminology

SSDT / BIDS

BIML / EzAPI

  • “Designtime”

Script / code writing

Package creation(metadata available)

  • Designtime
  • “Scripttime”
  • Runtime

Package execution

scripttime in ezapi
“Scripttime” in EzAPI
  • When EzAPI code is executed
  • Equivalent to designtime in SSDT / BIDS
  • Complete access to SSIS metadata
  • Utilization:
    • Create SQL tables on the fly
    • Perform operations depending on column data types, f.x:
      • Character conversion
      • String trimming
    • Inspect metadata while debugging code
ezapi classes
EzAPI classes
  • Out-of-the-box Ez*-classes, corresponding to SSIS objects:

Top level:

EzProject

EzPackage

Connection mgrs.:

Ez***CM

EzSqlOleDbCM

EzOracleOleDbCM

EzDb2OleDbCM

EzFlatFileCM

EzExcelCM

Control flow:

EzSequence

EzForLoop

EzForEachLoop

Tasks:

EzExecSqlTask

EzExecPackage

EzFileSystemTask

EzDataFlow

Components:

Ez***Source

Ez***Destination

EzSqlDestination (fast load)

EzDerivedColumn

EzDataConvert

EzMulticast

EzUnionAll

EzMerge

EzLookup

EzScript

…but feel free to create your own!

*** = OleDb / AdoNet

ezapi syntax c edition
EzAPI syntax (C# edition)

varmyContainer = newEzSequence(myPackage) { Name = “Step 1" };

Object reference

EzAPI class(Sequence Container)

Parent object reference

Object properties

Read as:“Create a new Sequence Container inside myPackage. Set the containers name to ‘Step 1’. Use the myContainer variable as a reference for the new Sequence Container.

ezapi syntax c edition1
EzAPI syntax (c# edition)

// Create package:

varmyPackage= newEzPackage() { Name = "BasicEzApiPackage" };

// Create OLE DB connection manager inside package:

varmyConMgr= newEzOleDbConnectionManager(myPackage)

{

Name = "DW Meta",

ConnectionString = "Provider=sqlncli11;Data Source=localhost;InitialCatalog=MSBIP_EzAP...

};

// Create Execute SQL task:

var estTest = newEzExecSqlTask(myPackage)

{

Name = "EST Test",

Connection = myConMgr,

SqlStatementSource = "SELECT 'test' AS [Dummy]"

};

// Save package:

myPackage.SaveToFile("BasicEzApiPackage.dtsx");

structuring ezapi code
Structuring EzAPI code
  • Use C# language features to structure code:

// Set up project:

var project = newEzProject() { Name = "SSIS_EzAPI" };

// Set up project connection managers:

varaxCM = Generator.GetAXConnectionManager(project);

vardwCM = Generator.GetDWConnectionManager(project);

// Add Extract packages for AX:

project.AddPackage(Generator.GenerateFullExtractPackage(axCM, "CUSTTABLE", dwCM, "AX_CUSTOMER"));

project.AddPackage(Generator.GenerateFullExtractPackage(axCM, "PRODUCT", dwCM, "AX_PRODUCT"));

project.AddPackage(Generator.GenerateFullExtractPackage(axCM, "SALESORDER", dwCM, "AX_SALESORDER"));

project.AddPackage(Generator.GenerateIncrementalExtractPackage(axCM, "INVENTTRANS", dwCM, "AX_INVENT...

// Add Load Dimension packages:

project.AddPackage(Generator.GenerateLoadDimensionPackage(dwCM, "Customer"));

project.AddPackage(Generator.GenerateLoadDimensionPackage(dwCM, "Product"));

project.AddPackage(Generator.GenerateLoadDimensionPackage(dwCM, "Calendar"));

project.SaveAs(OutputPath+ "CodeStructureProject.ispac");

  • Note the .ispac project file type (not .dtproj)
accessing scripttime metadata
Accessing “scripttime” metadata
  • After calling .AttachTo() on a Data Flow component, column metadata is available:

varderCmp = newEzDerivedColumn(dataFlowTask) { Name = "DER Transform"};derCmp.AttachTo(srcCmp);

varinputColumns = derCmp.Meta.InputCollection[0].InputColumnCollection;

foreach (varcol ininputColumns)

{

if(col.DataType== DataType.DT_WSTR)

derCmp.Expression[col.Name] = string.Format("RTRIM({0})", col.Name);

}

extending ezapi
Extending EzAPI
  • Create task and component C# classes
    • Wrap existing SSIS objects (FtpTask, WebTask, etc.)
    • Wrap 3rd party SSIS objects (KimballSCD, etc.)
  • Extend EzAPI with helper classes
    • EzAPIExtensions(available for download)
      • Create SQL tables on the fly
      • Various EzAPI improvements
      • More to come…
  • Create reusable templates
more ezapi
More EzAPI
  • Load existing SSIS projects and packages
  • Batch changes
  • Reverse engineering
ezapi references
EzAPI references
  • Get EzAPI from Codeplex:http://sqlsrvintegrationsrv.codeplex.com/releases/view/21238
  • SSIS-team blog:http://blogs.msdn.com/b/mattm/archive/2008/12/30/ezapi-alternative-package-creation-api.aspx
  • Other blogs:http://www.dimodelo.com/blog/category/ezapi/http://billfellows.blogspot.dk/2012/01/ezapi-overview.html
thanks for listening

Thanks for listening!

Slides, samples and tutorials coming soon on our blog. Stay tuned!

Feedback and questions:

do@kapacity.dk