In the previously published article, Biml alternatives: Building SSIS packages programmatically using ManagedDTS, we talked about building SSIS packages using the managed object model of the SSIS engine (ManagedDTS). In this article, we will illustrate another Biml alternative, which is the EzApi class library, and we will make a comparison between both technologies.
在先前发布的文章Biml 替代品：使用 ManagedDTS 编程地构建SSIS包中 ，我们讨论了使用SSIS引擎的托管对象模型（ManagedDTS）来构建SSIS包。 在本文中，我们将说明另一个Biml替代方案，即EzApi类库，并且将对这两种技术进行比较。
什么是EzAPI类库？ (What is the EzAPI class library?)
As we talked previously, Microsoft provides a set of assemblies that allows users to programmatically create, manage and execute SSIS packages. The main weakness of using these assemblies is that they are very complex and hard to understand. For this reason, the Microsoft SSIS team developed a .Net Class library called EzApi that facilitates automating SSIS package development. This class library is developed using C# and it was used internally by the team for a while. Later it was published on CodePlex within the Integration Services community samples project and later, it was migrated to Git-Hub after the Code Plex website is closed, but this project is not improved for a while.
如前所述，Microsoft提供了一组程序集，允许用户以编程方式创建，管理和执行SSIS包。 使用这些程序集的主要缺点是它们非常复杂且难以理解。 因此，Microsoft SSIS团队开发了一个名为EzApi的.Net类库，该库有助于自动化SSIS包开发。 该类库是使用C＃开发的，并且在团队内部已使用了一段时间。 后来，它在Integration Services社区示例项目中的CodePlex上发布 ，后来，在Code Plex网站关闭后， 它又迁移到Git-Hub ，但此项目暂时没有得到改善。
After SQL Server 2016 was released, Pedros Morais (a Full Stack developer) announced EzApi2016, a fork of the original project to adapt this library to the new SQL Server version. And later, this library is available on NuGet and it is improved periodically. More information about the improvement can be found on the Pedro Morais website.
在SQL Server 2016发布之后，Pedros Morais（全栈开发人员）宣布了EzApi2016 ，它是原始项目的分支，旨在使该库适应新SQL Server版本。 之后，该库可在NuGet上使用 ，并且会定期进行改进。 有关改进的更多信息，请访问Pedro Morais网站。
This class library can be considered as an intermediate layer between the application and the SSIS managed object model. Each object in this library is mapped to the related COM object in SSIS. You can check the runtime and data flow engines class hierarchy in the following images (Reference: EzAPI – Alternative package creation API)
可以将此类库视为应用程序和SSIS受管对象模型之间的中间层。 该库中的每个对象都映射到SSIS中的相关COM对象。 您可以在以下图像中检查运行时和数据流引擎类的层次结构（参考： EzAPI –替代程序包创建API ）
As shown in the runtime engine class hierarchy, the base entity is the executable, which can be a container or a task. And in the data flow engine, the base entity is a component which can be an adapter (source or destination – since they adapt SSIS to an external source) or a transformation.
如运行时引擎类层次结构所示，基本实体是可执行文件，可以是容器或任务。 在数据流引擎中，基础实体是可以作为适配器（源或目标，因为它们使SSIS适应外部源）或转换的组件。
使用NuGet安装 (Installation using NuGet)
The easiest way to use this class library is to open the NuGet package manager console within Visual studio (Tools > NuGet Package Manager > Package Manager Console).
使用此类库的最简单方法是在Visual Studio中打开NuGet包管理器控制台（“工具”>“ NuGet包管理器”>“包管理器控制台”）。
Then you should use the following command (0.8.93 is the latest version right now):
然后，您应该使用以下命令（0.8.93是当前的最新版本）：
Install-Package EzApi -Version 0.8.93
Now, the class library is added as project reference:
现在，添加了类库作为项目引用：
If you are working offline, you can simply download and build the project locally, then add the generated class library as a reference within your project.
如果您离线工作，则只需在本地下载并构建项目，然后将生成的类库添加为项目中的引用。
使用EzApi构建软件包 (Building packages using EzApi)
Before getting started, you should note that you may need to add a reference for the SSIS managed object model assemblies to the project since it is required by some of the methods in the class library.
在开始之前，您应该注意，您可能需要为项目中的SSIS管理对象模型程序集添加引用，因为类库中某些方法要求使用该引用。
创建一个新包 (Creating a new package)
First of all, you should import the Microsoft.SqlServer.SSIS.EzAPI namespace within the current class:
首先，您应该在当前类中导入Microsoft.SqlServer.SSIS.EzAPI命名空间：
using Microsoft.SqlServer.SSIS.EzAPI;
To create a new package and save it within a local path you can use the following code:
要创建新程序包并将其保存在本地路径中，可以使用以下代码：
EzPackage ezPackage = new EzPackage();
ezPackage.Name = "EzAPIPackage";
ezPackage.SaveToFile(@"D:\EzAPIPackage.dtsx");
从现有文件加载包 (Loading package from existing file)
To load a package from an existing dtsx file, you can use the following code:
要从现有的dtsx文件加载程序包，可以使用以下代码：
EzPackage ezPackage = null;
ezPackage = new EzPackage();
ezPackage.LoadFromFile(@"D:\EzAPIPackage.dtsx");
添加连接管理器 (Adding Connection managers)
In order to add a connection manager, you should use EzConnectionManager or the relevant connection manager class as following:
为了添加连接管理器，应使用EzConnectionManager或相关的连接管理器类，如下所示：
EzOleDbConnectionManager EzOleDbConMgr = new EzOleDbConnectionManager(ezPackage);
EzOleDbConMgr.Name = "SourceOLEDBConnection";
EzOleDbConMgr.ConnectionString = @"Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI;Provider=SQLNCLI11;Auto Translate=false;";
添加任务 (Adding tasks)
To add a task within a container (package or container), you should use the task-related class and specify the parent container on the class initialization. For example:
要在容器（包装或容器）中添加任务，应使用与任务相关的类，并在类初始化时指定父容器。 例如：
EzExecSqlTask SQLTask = new EzExecSqlTask(ezPackage);
SQLTask.Connection = EzOleDbCon;
SQLTask.SqlStatementSourceType = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.SqlStatementSourceType.DirectInput;
SQLTask.SqlStatementSource = "SELECT * FROM Person.Person";
ezPackage.SaveToFile(@"D:\EzAPIPackage.dtsx");
添加数据流任务 (Adding Data Flow Tasks)
Using Ezapi, adding tasks is more easier than SSIS managed object model. The following code is used to create a package, adding an OLE DB connection manager, adding a data flow task with an OLE DB Source and destination in order to transfer data between [Person].[Person] and [Person].[Person_temp] tables:
使用Ezapi，添加任务比SSIS受管对象模型更容易。 以下代码用于创建程序包，添加OLE DB连接管理器，添加带有OLE DB源和目标的数据流任务，以便在[Person]。[Person]和[Person]之间传输数据。[Person_temp]表格：
//Creating package
EzPackage ezPackage = null;
ezPackage = new EzPackage();
ezPackage.Name = "EzAPIPackage";
//Adding an OLE DB connection manager
EzOleDbConnectionManager EzOleDbConMgr = new EzOleDbConnectionManager(ezPackage);
EzOleDbConMgr.Name = "SourceOLEDBConnection";
EzOleDbConMgr.ConnectionString = @"Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI;Provider=SQLNCLI11;Auto Translate=false;";
//Adding a data flow task
EzDataFlow dataflow = new EzDataFlow(ezPackage);
//Adding an OLE DB Source
EzOleDbSource source = new EzOleDbSource(dataflow);
source.Connection = EzOleDbConMgr;
source.Table = "[Person].[Person]";
source.AccessMode = AccessMode.AM_OPENROWSET;
//Adding an OLE DB Destination
EzOleDbDestination destination = new EzOleDbDestination(dataflow);
destination.Connection = EzOleDbConMgr;
destination.AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD;
destination.Table = "[Person].[Person_temp]";
//Linking source and destination
destination.AttachTo(source);
destination.LinkAllInputsToOutputs();
//Saving package to a dtsx file
ezPackage.SaveToFile(@"D:\EzAPIPackage.dtsx");
执行包 (Executing packages)
To execute a package, you can simply use the Execute() method as follows:
要执行程序包，只需使用Execute（）方法，如下所示：
ezPackage.Execute();
包模板 (Package templates)
There are some template packages added within this class library. As example:
在该类库中添加了一些模板包。 例如：
- EzDataFlowPackage: a package that contains a data flow task EzDataFlowPackage ：一个包含数据流任务的包
- EzForLoopDFPackage: a package that contains a for loop with a data flow task EzForLoopDFPackage ：一个包，其中包含带有数据流任务的for循环
- EzForLoopPackage: a package that contains a for loop container EzForLoopPackage ：一个包含for循环容器的软件包
These templates decrease the time and lines of codes needed to develop packages.
这些模板减少了开发软件包所需的时间和代码行。
在线资源 (Online Resources)
EzApi is not very popular, but there is some article online that you can refer to in order to learn more:
EzApi不是很受欢迎，但是您可以参考一些在线文章以了解更多信息：
- Microsoft tech community website 微软技术社区网站
- Archived Microsoft Blogs – Paul Rizza’s T-SQL, SSIS and SSRS Blog 存档的Microsoft博客– Paul Rizza的T-SQL，SSIS和SSRS博客
- Archived Microsoft Blogs – SSIS 2012 EzAPI – First Real Package 存档的Microsoft博客– SSIS 2012 EzAPI –第一个实际包
- Article published by Bill Fellows on his blog Bill Fellows在他的博客上发表的文章
- Article published on Dimodelo solutions website 文章发表在Dimodelo解决方案网站上
- Article published by Kapacity business analytics website Kapacity商业分析网站发表的文章
- Related question on Stackoverflow 有关Stackoverflow的相关问题
与Biml比较 (Comparing with Biml)
简单 (Simplicity)
After creating the package, we will convert it to a Biml script, as we explained in Converting SSIS packages to Biml scripts article
创建软件包后，我们将其转换为Biml脚本，如将SSIS软件包转换为Biml脚本文章中所述
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<Connection Name="SourceOLEDBConnection" ConnectionString="Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI;Provider=SQLNCLI11;Auto Translate=false;" />
</Connections>
<Packages>
<Package Name="{F9205928-FD01-4C5C-B444-52424DB72B88}" Language="None" ConstraintMode="LinearOnCompletion" ProtectionLevel="EncryptSensitiveWithUserKey">
<Tasks>
<Dataflow Name="Import Person">
<Transformations>
<OleDbSource Name="OLE DB Source" ConnectionName="ConnectionOLEDB">
<DirectInput>Select * from Person.Person</DirectInput>
</OleDbSource>
<OleDbDestination Name="OLE DB Destination" ConnectionName="ConnectionOLEDB">
<ExternalTableOutput Table="[Person].[Person_temp]" />
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>
As you can see, Biml script is a bit simple that the C# code we wrote previously. We can say that both technologies used facilitates the SSIS package automation much more than using the traditional assemblies provided by Microsoft. And it depends on the user programming background, since developers will found themselves more familiar with EzAPI, while other employees or analyst will go Biml.
如您所见，Biml脚本与我们之前编写的C＃代码相比有点简单。 可以说，与使用Microsoft提供的传统程序集相比，所使用的两种技术都大大促进了SSIS包的自动化。 而且这取决于用户编程的背景，因为开发人员会发现自己对EzAPI更加熟悉，而其他员工或分析师将使用Biml。
语境 (Context)
As we mentioned in the previous article, if you need to automate package creation and management within an application, then you cannot go with Biml. In other cases, you are free to choose between both technologies.
正如我们在上一篇文章中提到的那样，如果您需要在应用程序内自动进行包的创建和管理，则不能使用Biml。 在其他情况下，您可以自由选择两种技术。
开发中的错误处理 (Error handling in development)
EzAPI has the same limitation than ManagedDTS since it doesn’t show a clear error message if encountered while building the package:
EzAPI与ManagedDTS具有相同的限制，因为在构建软件包时如果遇到EzAPI不会显示明确的错误消息：
在线资源 (Online Resources)
Even if Biml doesn’t have plenty of resources online, but they are much more than the one related to EzAPI since you may not find more articles than the ones we mentioned previously (even those articles take a lot of time to be recognized).
即使Biml在线上没有足够的资源，但是它们远远超过了与EzAPI相关的资源，因为您可能找不到比我们之前提到的文章更多的文章（即使那些文章也需要花费很多时间才能被认可）。
结论 (Conclusion)
In this article, we have illustrated another alternative of Biml called EzAPI, we talked briefly about this library and why it was developed, then we explained how to use it to create and manage SSIS packages. Finally, we made a comparison between both technologies.
在本文中，我们说明了Biml的另一个替代方案，称为EzAPI，我们简要讨论了该库及其开发原因，然后说明了如何使用它来创建和管理SSIS包。 最后，我们对两种技术进行了比较。
目录 (Table of contents)
| Getting started with Biml |
| Converting SSIS packages to Biml scripts |
| Using Biml scripts to generate SSIS packages |
| Extending Biml with C# scripts |
| Using external C# script files within Biml |
| Biml alternatives: Building SSIS packages programmatically using ManagedDTS |
| Biml alternatives: Building SSIS packages programmatically using EzAPI |
| Biml入门 |
| 将SSIS包转换为Biml脚本 |
| 使用Biml脚本生成SSIS包 |
| 使用C＃脚本扩展Biml |
| 在Biml中使用外部C＃脚本文件 |
| Biml替代品：使用ManagedDTS以编程方式构建SSIS包 |
| Biml替代品：使用EzAPI以编程方式构建SSIS包 |
翻译自: https://www.sqlshack.com/biml-alternatives-building-ssis-packages-programmatically-using-ezapi/
614
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
