与EZApi自动映射列与OLEDBSource [英] Automatically mapping columns with EZApi with OLEDBSource

问题描述

使用EZApi，一个EzOleDbSource对象被创建，并分配表属性。
另一个任务添加（EzDerivedColumn）和附连到EzOleDbSource对象。
当我打开包，我需要点击OLEDB源获得列输出到派生任务的输入。

有没有以编程方式设置使用EzApi输入和输出映射的方法吗？注：我使用LinkAllInputsToOutputs（）;

代码：

  EzSqlOleDbCM RefConn =新EzSqlOleDbCM（包，dataVault_ConMgr）; 
 EzOleDbSource ezOleDbSource_SatFromStaging =新EzOleDbSource（satelliteDft）; 
 ezOleDbSource_SatFromStaging.Table = formatSQLTableName（settings.bd_datavault_schema_staging，stgTable）; 
 ezOleDbSource_SatFromStaging.Name = settings.ssis_prefix_dataflow_oledb_source + stgTable; 
  

ezOleDbSource_SatFromStaging.LinkAllInputsToOutputs（）;

  EzDerivedColumn ezDerivedColumn =新EzDerivedColumn（satelliteDft）; 
 ezDerivedColumn.LinkAllInputsToOutputs（）; 
 ezDerivedColumn.AttachTo（ezOleDbSource_SatFromStaging）; 
 ezDerivedColumn.Name = settings.ssis_prefix_task_derived + stgTable; 
  

解决方案

可能不是最好的代码，但是这是为我工作。它产生与具有OLE DB源，一个出列，只是做了串并写入表中的数据流的包。

我得到一些警告和信息消息，但我认为，随着编程派生列工作，没有足够的时间的原因。

  ///<总结> 
 ///创建与从表src_dWolf 
 ///&所述拉一个数据流的包;示例> 
 /// CREATE TABLE dbo.src_dWolf 
 ///（
 /// le_key INT NOT NULL PRIMARY KEY 
 ///，le_value VARCHAR（50）NOT NULL 
 ///）; 
 /// 
 /// CREATE TABLE dbo.dst_dWolf 
 ///（
 /// le_key INT NOT NULL PRIMARY KEY 
 ///，le_value VARCHAR （50）NOT NULL 
 ///，le_newValue VARCHAR（20）NOT NULL 
 ///）; 
 /// 
 /// INSERT INTO dbo.src_dWolf 
 ///（
 /// le_key 
 ///，le_value 
 // /）
 /// VALUES 
 ///（
 /// 10 
 ///ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTU'
 ///）; 
 ///< /示例> 
 ///< /总结> 
公共静态无效的演示（）
 {
串dataVault_ConMgr = @数据源= localhost\DEV2012;供应商= SQLNCLI10.1;集成安全性= SSPI;初始目录= TypeMoreClickLess;; 
 EzPackage包=新EzPackage（）; 
串stgTable =src_dWolf; 
串bd_datavault_schema_staging =DBO; 
串ssis_prefix_dataflow_oledb_source =SRC; 
串ssis_prefix_task_derived =DST; 
 
 EzDataFlow satelliteDft =新EzDataFlow（包）; 
 satelliteDft.Name =DFT演示; 
 
 EzSqlOleDbCM RefConn =新EzSqlOleDbCM（包，dataVault_ConMgr）; 
 RefConn.Name =TMCL; 
 RefConn.ConnectionString = dataVault_ConMgr; 
 
 EzOleDbSource ezOleDbSource_SatFromStaging =新EzOleDbSource（satelliteDft）; 
 ezOleDbSource_SatFromStaging.Table =的String.Format（[{0}] [{1}]。bd_datavault_schema_staging，stgTable）; 
 ezOleDbSource_SatFromStaging.Name = ssis_prefix_dataflow_oledb_source + stgTable; 
 ezOleDbSource_SatFromStaging.Connection = RefConn; 
 
 EzDerivedColumn ezDerivedColumn =新EzDerivedColumn（satelliteDft）; 
 ezDerivedColumn.Name = ssis_prefix_task_derived + stgTable; 
 
 ezDerivedColumn.InsertOutputColumn（le_newValue）; 
 ezDerivedColumn.SetOutputColumnDataTypeProperties（le_newValue，Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR，20，0，0，1252）; 
 // http://social.msdn.microsoft.com/Forums/sqlserver/en-US/137af5f4-3d35-45c2-9a3f-2127dc98fb6c/ezapi-how-to-working-with-ezderivedcolumn?forum= sqlintegrationservices 
 Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutputColumn100 derCol = ezDerivedColumn.OutputCol（le_newValue）; 
 derCol.CustomPropertyCollection [FriendlyExpression]值=SUBSTRING（[le_value]，1，20）。 
 derCol.CustomPropertyCollection [表达式]值=SUBSTRING（[le_value]，1，20）。 
 
 ezDerivedColumn.AttachTo（ezOleDbSource_SatFromStaging）; 
 
 EzOleDbDestination ezOleDbDestination =新EzOleDbDestination（satelliteDft）; 
 ezOleDbDestination.Name =DST dst_dWolf 
 ezOleDbDestination.Table =[DBO] [dst_dWolf。 
 ezOleDbDestination.Connection = RefConn; 
 ezOleDbDestination.FastLoadKeepIdentity = TRUE; 
 ezOleDbDestination.FastLoadKeepNulls = TRUE; 
 ezOleDbDestination.FastLoadOptions =TABLOCK，CHECK_CONSTRAINTS 
 ezOleDbDestination.AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD; 
 ezOleDbDestination.AttachTo（ezDerivedColumn）; 
 ezOleDbDestination.LinkAllInputsToOutputs（）; 
 
 
 package.SaveToFile（@C：\sandbox\TypeMoreClickLess\EzAPIDemo\dwolf.dtsx）; 
} 
  

Using EZApi, an EzOleDbSource object is created, and assigned the Table property. Another task is added (EzDerivedColumn) and attached to the EzOleDbSource object. When I open the package, I need to click on the OLEDB source to get the column output to the input of the derived task.

Is there a way to programatically set the input and output mappings using EzApi? Note: I am using LinkAllInputsToOutputs();

Code:

        EzSqlOleDbCM RefConn = new EzSqlOleDbCM(package, dataVault_ConMgr);
        EzOleDbSource ezOleDbSource_SatFromStaging = new EzOleDbSource(satelliteDft);
        ezOleDbSource_SatFromStaging.Table = formatSQLTableName(settings.bd_datavault_schema_staging, stgTable);
        ezOleDbSource_SatFromStaging.Name = settings.ssis_prefix_dataflow_oledb_source + stgTable;

ezOleDbSource_SatFromStaging.LinkAllInputsToOutputs();

        EzDerivedColumn ezDerivedColumn = new EzDerivedColumn(satelliteDft);
        ezDerivedColumn.LinkAllInputsToOutputs(); 
        ezDerivedColumn.AttachTo(ezOleDbSource_SatFromStaging);
        ezDerivedColumn.Name = settings.ssis_prefix_task_derived + stgTable;

解决方案

Probably not the best code but this is working for me. It generates a package with a data flow that has an OLE DB Source, a Derived Column that just does a substring and writes to a table.

I get some warnings and information messages but I think that's due to not enough time working with derived columns programmatically.

    /// <summary>
    /// Create a package with a data flow that pulls from table src_dWolf
    /// <example>
    /// CREATE TABLE dbo.src_dWolf
    /// (
    ///     le_key int NOT NULL PRIMARY KEY
    /// ,   le_value varchar(50) NOT NULL  
    /// );
    ///
    /// CREATE TABLE dbo.dst_dWolf
    /// (
    ///     le_key int NOT NULL PRIMARY KEY
    /// ,   le_value varchar(50) NOT NULL  
    /// ,   le_newValue varchar(20) NOT NULL
    /// );
    /// 
    /// INSERT INTO dbo.src_dWolf
    /// (
    ///     le_key
    /// ,   le_value
    /// )
    /// VALUES
    /// (
    ///     10
    /// ,   'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTU'
    /// );
    /// </example>
    /// </summary>
    public static void Demo()
    {
        string dataVault_ConMgr = @"Data Source=localhost\DEV2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Initial Catalog=TypeMoreClickLess;";
        EzPackage package = new EzPackage();
        string stgTable = "src_dWolf";
        string bd_datavault_schema_staging = "dbo";
        string ssis_prefix_dataflow_oledb_source = "SRC ";
        string ssis_prefix_task_derived = "DST ";

        EzDataFlow satelliteDft = new EzDataFlow(package);
        satelliteDft.Name = "DFT demo";

        EzSqlOleDbCM RefConn = new EzSqlOleDbCM(package, dataVault_ConMgr);
        RefConn.Name = "TMCL";
        RefConn.ConnectionString = dataVault_ConMgr;

        EzOleDbSource ezOleDbSource_SatFromStaging = new EzOleDbSource(satelliteDft);
        ezOleDbSource_SatFromStaging.Table = String.Format("[{0}].[{1}]", bd_datavault_schema_staging, stgTable);
        ezOleDbSource_SatFromStaging.Name = ssis_prefix_dataflow_oledb_source + stgTable;
        ezOleDbSource_SatFromStaging.Connection = RefConn;

        EzDerivedColumn ezDerivedColumn = new EzDerivedColumn(satelliteDft);
        ezDerivedColumn.Name = ssis_prefix_task_derived + stgTable;

        ezDerivedColumn.InsertOutputColumn("le_newValue");
        ezDerivedColumn.SetOutputColumnDataTypeProperties("le_newValue", Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR, 20, 0, 0, 1252);
        // http://social.msdn.microsoft.com/Forums/sqlserver/en-US/137af5f4-3d35-45c2-9a3f-2127dc98fb6c/ezapi-how-to-working-with-ezderivedcolumn?forum=sqlintegrationservices
        Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutputColumn100 derCol = ezDerivedColumn.OutputCol("le_newValue");
        derCol.CustomPropertyCollection["FriendlyExpression"].Value = "SUBSTRING([le_value], 1, 20 )";
        derCol.CustomPropertyCollection["Expression"].Value = "SUBSTRING([le_value], 1, 20 )";

        ezDerivedColumn.AttachTo(ezOleDbSource_SatFromStaging);

        EzOleDbDestination ezOleDbDestination = new EzOleDbDestination(satelliteDft);
        ezOleDbDestination.Name = "DST dst_dWolf";
        ezOleDbDestination.Table = "[dbo].[dst_dWolf]";
        ezOleDbDestination.Connection = RefConn;
        ezOleDbDestination.FastLoadKeepIdentity = true;
        ezOleDbDestination.FastLoadKeepNulls = true;
        ezOleDbDestination.FastLoadOptions = "TABLOCK,CHECK_CONSTRAINTS";
        ezOleDbDestination.AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD;
        ezOleDbDestination.AttachTo(ezDerivedColumn);
        ezOleDbDestination.LinkAllInputsToOutputs();


        package.SaveToFile(@"C:\sandbox\TypeMoreClickLess\EzAPIDemo\dwolf.dtsx");
    }

这篇关于与EZApi自动映射列与OLEDBSource的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！