1 / 39

BIML & EzAPI

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

leon
Download Presentation

BIML & EzAPI

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. 2013-09-05 BIML & EzAPI

  2. BIML and EzAPI Two approaches to creating SSIS packages programmatically Daniel Otykier // do@kapacity.dk

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

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

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

  6. Whywork with SSIS programmatically?

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

  8. Business Intelligence Markup Language BIML

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

  10. Using BIML • Make sure BIDS Helper is installed! • Create .biml files • Check for errors • Execute BIML Script = “Generate SSIS packages”

  11. Visual Studio hints • biml.xsd for syntax highlighting / IntelliSense • Disable xml formatting on paste:

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

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

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

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

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

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

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

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

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

  21. More BIML • BIML supports most SSIS features: • Variables • Expressions • Configurations • Events

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

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

  24. SSIS API wrapper written in C# EzAPI

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

  26. Terminology SSDT / BIDS BIML / EzAPI • “Designtime” Script / code writing Package creation(metadata available) • Designtime • “Scripttime” • Runtime Package execution

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

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

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

  30. 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");

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

  32. 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); }

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

  34. More EzAPI • Load existing SSIS projects and packages • Batch changes • Reverse engineering

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

  36. BIML and EzAPI Summary and discussion

  37. BIML vs. EzAPI BIML EzAPI

  38. Learning curves

  39. Thanks for listening! Slides, samples and tutorials coming soon on our blog. Stay tuned! Feedback and questions: do@kapacity.dk

More Related