Transcript Inputs til kapacity.dk

2013-09-05
BIML & EzAPI
BIML and EzAPI
Two approaches to creating SSIS
packages programmatically
Daniel Otykier // [email protected]
Agenda
•
•
•
•
About me
Why SSIS programmatically?
Prerequisites
BIML
– Introduction
– Samples
– References
• EzAPI
– Introduction
– Samples
– References
• Summary and discussion
– Strength and weaknesses
– Comparing BIML and EzAPI
About me
• Daniel Otykier [email protected]
• 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 Kapacity
since February 2013
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
Why work with SSIS programmatically?
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
Business Intelligence Markup Language
BIML
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
• Make sure BIDS Helper is installed!
– Create .biml files
– Check for errors
– Execute BIML Script = “Generate SSIS packages”
Visual Studio hints
• biml.xsd for syntax highlighting / IntelliSense
• Disable xml formatting on paste:
BIML syntax
Creating a package that contains a single data flow task:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="MyTestPackage" ConstraintMode="Linear">
<Tasks>
<Dataflow Name="My Data Flow">
<!-- ...dataflow components here... -->
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>
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
• Inline C# or VB.NET scripting with <# and #>
– Same as T4 templates
• Example: C# variables
<#@ template language="C#" hostspecific="true"#>
<# var packageName = "TestPackage"; #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="<#=packageName#>" ConstraintMode="Linear"/>
</Packages>
</Biml>
BIMLScript basics
• Example: C# conditionals
<#@ template language="C#" hostspecific="true"#>
<# var value = 1; #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<# if (value == 1) { #>
<Package Name="PackageA" ConstraintMode="Linear"/>
<# } else { #>
<Package Name="PackageB" ConstraintMode="Linear"/>
<# } #>
</Packages>
</Biml>
BIMLScript basics
• Example: C# loops
<#@ template language="C#" hostspecific="true"#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<# for (var i = 1; i <= 5; i++) { #>
<Package Name="Package <#=i#>" ConstraintMode="Linear"/>
<# } #>
</Packages>
</Biml>
• When running this BIML, it is expanded into…
BIMLScript loop expansion
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<Package Name="Package 1" ConstraintMode="Linear"/>
<Package Name="Package 2" ConstraintMode="Linear"/>
<Package Name="Package 3" ConstraintMode="Linear"/>
<Package Name="Package 4" ConstraintMode="Linear"/>
<Package Name="Package 5" ConstraintMode="Linear"/>
</Packages>
</Biml>
• All inline C# code is gone!
• …and then SSIS packages are generated.
Using metadata
<#@ template language="C#" hostspecific="true" #>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Packages>
<#
var metadataConnection = "Provider=SQLNCLI11;Server=localhost;Initial Cata
var metadataSql = "SELECT PackageName FROM Packages";
var metadataTable = ExternalDataAccess.GetDataTable(metadataConnection,
metadataSql);
foreach (DataRow row in metadataTable.Rows)
{ #>
<Package Name="<#=row["PackageName"]#>" ConstraintMode="Linear">
</Package>
<# } #>
</Packages>
</Biml>
Repeated for every row in table “Packages”
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
• 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
• BIML supports most SSIS features:
– Variables
– Expressions
– Configurations
– Events
More BIML
• BIML supports new SSIS 2012 features:
– Project Connection Managers:
<OleDbConnection Name="OLTP" ConnectionString=”...”
CreateInProject="true"/>
– Package Parameters:
<Parameter DataType="String" Name=”MyParam">MyValue</Parameters>
– Use project/package parameters just like variables:
<Variable Name=”MyVariable" DataType="String”
EvaluateAsExpression="true">@[$Package::MyParam]
</Variable>
BIML references
BIML has an active user community:
• Varigence.com
http://www.varigence.com/Documentation/Samples/Biml
• BIMLScript.com
http://www.bimlscript.com/Browse/Snippets
• bidshelper.codeplex.com:
http://bidshelper.codeplex.com/wikipage?title=Samples%20and%20Tutorials
SSIS API wrapper written in C#
EzAPI
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
SSDT / BIDS
BIML / EzAPI
“Designtime”
Script / code writing
Package creation
(metadata available)
Package execution
Designtime
“Scripttime”
Runtime
“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
• 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)
var myContainer = new EzSequence(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# edition)
// Create package:
var myPackage = new EzPackage() { Name = "BasicEzApiPackage" };
// Create OLE DB connection manager inside package:
var myConMgr = new EzOleDbConnectionManager(myPackage)
{
Name = "DW Meta",
ConnectionString = "Provider=sqlncli11;Data Source=localhost;Initial Catalog=MSBIP_EzAP...
};
// Create Execute SQL task:
var estTest = new EzExecSqlTask(myPackage)
{
Name = "EST Test",
Connection = myConMgr,
SqlStatementSource = "SELECT 'test' AS [Dummy]"
};
// Save package:
myPackage.SaveToFile("BasicEzApiPackage.dtsx");
Structuring EzAPI code
• Use C# language features to structure code:
// Set up project:
var project = new EzProject() { Name = "SSIS_EzAPI" };
// Set up project connection managers:
var axCM = Generator.GetAXConnectionManager(project);
var dwCM = 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
• After calling .AttachTo() on a Data Flow
component, column metadata is available:
var derCmp = new EzDerivedColumn(dataFlowTask) { Name = "DER Transform" };
derCmp.AttachTo(srcCmp);
var inputColumns = derCmp.Meta.InputCollection[0].InputColumnCollection;
foreach (var col in inputColumns)
{
if(col.DataType == DataType.DT_WSTR)
derCmp.Expression[col.Name] = string.Format("RTRIM({0})", col.Name);
}
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
• Load existing SSIS projects and packages
• Batch changes
• Reverse engineering
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
BIML and EzAPI
Summary and discussion
BIML vs. EzAPI
BIML
EzAPI
Pros
Pros
•
•
•
•
•
•
•
•
•
No licensing (included in BIDShelper)
Intuitive for experienced SSIS users
Many tutorials and samples online
Integrates nicely in SSIS projects
Goes beyond SSIS
Very flexible and extensible
Access metadata at scripttime
Make changes to existing packages
C# and object-oriented programming
Cons
Cons
• Limited to standard functionality /
not extensible
• BIMLScript XML may grow
cumbersome and difficult to read
and maintain
• Requires Visual Studio license
• C# and object-oriented programming
• Few tutorials online
Learning curves
Thanks for listening!
Slides, samples and tutorials coming
soon on our blog. Stay tuned!
Feedback and questions:
[email protected]