我到处搜寻了,现在必须要问.我正在尝试使用EzAPI构造一个简单的数据流.一切都不是一件容易的事,但是我致力于解决这个问题.我不知道如何使EzOleDBDestination工作.这是我完整的代码
var a = new Application();
// using a template since it's impossible to set up an ADO.NET connection to MySQL
// using EzAPI and potentially even with the raw SSIS API...
var pkg = new EzPackage(a.LoadPackage(@"C:\...\Package.dtsx", null));
pkg.Name = "Star";
var df = new EzDataFlow(pkg);
df.Name = "My DataFlow";
var src = new EzAdoNetSource(df);
src.Name = "Source Database";
src.SqlCommand = "SELECT * FROM enum_institution";
src.AccessMode = AccessMode.AM_SQLCOMMAND;
src.Connection = new EzConnectionManager(pkg, pkg.Connections["SourceDB"]);
src.ReinitializeMetaData();
var derived = new EzDerivedColumn(df);
derived.AttachTo(src);
derived.Name = "Prepare Dimension Attributes";
derived.LinkAllInputsToOutputs();
derived.Expression["SourceNumber"] = "id";
derived.Expression["Name"] = "(DT_STR,255,1252)description";
// EDIT: reordered the operation here and I no longer get an error, but
// I'm not getting any mappings or any input columns when I open the package in the designer
var dest = new EzOleDbDestination(df);
dest.AttachTo(derived, 0, 0);
dest.Name = "Target Database";
dest.AccessMode = 0;
dest.Table = "[dbo].[DimInstitution]";
dest.Connection = new EzConnectionManager(pkg, pkg.Connections["TargetDB"]);
// this comes from Yahia's link
var destInput = dest.Meta.InputCollection[0];
var destVirInput = destInput.GetVirtualInput();
var destInputCols = destInput.InputColumnCollection;
var destExtCols = destInput.ExternalMetadataColumnCollection;
var sourceColumns = derived.Meta.OutputCollection[0].OutputColumnCollection;
foreach(IDTSOutputColumn100 outputCol in sourceColumns) {
// Now getting COM Exception here...
var extCol = destExtCols[outputCol.Name];
if(extCol != null) {
// Create an input column from an output col of previous component.
destVirInput.SetUsageType(outputCol.ID, DTSUsageType.UT_READONLY);
var inputCol = destInputCols.GetInputColumnByLineageID(outputCol.ID);
if(inputCol != null) {
// map the input column with an external metadata column
dest.Comp.MapInputColumn(destInput.ID, inputCol.ID, extCol.ID);
}
}
}
基本上,任何涉及到对ReinitializeMetadata()的调用的结果都是0xC0090001,因为该方法是发生错误的地方.没有真正的文档可以帮助我,因此在这里我必须依靠任何专家.
我应该提到源数据库是MySQL,目标数据库是SQL Server.使用SSIS设计器构建这样的程序包效果很好,所以我知道这是可能的.
随时告诉我我是否做错了其他任何事情.
编辑:这是我用作模板的基本软件包的链接:http://www.filedropper.com/package_1.我已经编辑了连接细节,但是任何MySQL和SQL Server数据库都可以.该包将从MySQL中读取(使用MySQL ADO.NET连接器)并写入SQL Server.
数据库模式基本上无关紧要.为了进行测试,只需在MySQL中创建一个包含两列的表:id(int)和description(varchar),其中id是主键.在SQL Server中创建等效列.这里的目标只是简单地从一个复制到另一个.在某些时候它可能最终变得更加复杂,但是我必须首先克服这个障碍.
解决方法:
我现在无法测试,但我敢肯定,以下内容将帮助您使其正常工作:
> Calling ReinitializeMetadata()
causes the component to fetch the table metadata. This should only be called after setting the AccessMode
and related property.您在设置AccessMode之前正在调用它…
> Various samples including advice on debugging problems
>直接在SQL命令中定义派生列,而不使用EzDerivedColumn
>尝试首先使其与2个SQL Server数据库一起使用,某些情况下某些可用的MySQL ADO.NET提供程序存在一些缺点
UPDATE-根据评论,提供有关调试此功能的更多信息,以及指向带有源代码的完整端到端示例的链接:
> http://blogs.msdn.com/b/mattm/archive/2009/08/03/looking-up-ssis-hresult-comexception-errorcode.aspx
> http://blogs.msdn.com/b/mattm/archive/2009/08/03/debugging-a-comexception-during-package-generation.aspx
> Complete working sample with source