什么是EzAPI相当于使用OLE DB源命令将从变量？ [英] What is the EzAPI equivalent for using an OLE DB Source command from variable?

问题描述

什么是EzAPI代码使用OLE DB源以自变量的SQL命令数据访问模式和分配一个变量

What is the EzAPI code to use an OLE DB Source with data access mode of "SQL command from variable" and assign a variable?

每月一次，我们需要更新我们的公共试验场生产数据的子集。我们已经确定了我们的需求，一个SSIS解决方案为完成这一任务最合适的。

Once a month, we need to refresh our public test site with subsets of production data. We have determined that for our needs, an SSIS solution provides the best fit for accomplishing this task.

我的目标是系统建设的大量（100+）复制的包。 EzAPI 是一个友好的包装器的 SSIS对象模型和它似乎是一个伟大的方式节省的鼠标点击。

My goal is to systematically build a large number (100+) of "replication" packages. EzAPI is a friendly wrapper to the SSIS object model and it seems like a great way to save mouse-clicks.

我想为我的包看起来像

• 变量 - tableName值; [架构] [表名]


• 变量 - sourceQuery。 SELECT * FROM [架构] [表名]


• 数据流 - 复制Schema_TableName


• 
  
  • OLE DB来源 - 源Schema_TableName数据访问模式：从变量SQL命令;变量名：用户:: sourceQuery
  
  
  • Variable - "tableName"; [Schema].[TableName]
  • Variable - "sourceQuery"; SELECT * FROM [Schema].[TableName]
  • DataFlow - "Replicate Schema_TableName"
  • • OLE DB Source - "Src Schema_TableName"; Data Access Mode: SQL command from variable; Variable name: User::sourceQuery
    
    
    • OLE DB目标 - 目标Schema_TableName表或视图名称可变快速的负载;变量名 - 用户:: tableName值
    
    

    这是我的表，表复制包的代码。

    This is the code for my table to table replication package.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.SqlServer.SSIS.EzAPI;
    using Microsoft.SqlServer.Dts.Runtime;
    
    namespace EzApiDemo
    {
        public class TableToTable : EzSrcDestPackage<EzOleDbSource, EzSqlOleDbCM, EzOleDbDestination, EzSqlOleDbCM>
        {
            public TableToTable(Package p) : base(p) { }
    
            public static implicit operator TableToTable(Package p) { return new TableToTable(p); }
    
    
            public TableToTable(string sourceServer, string database, string table, string destinationServer) : base()
            {
                string saniName = TableToTable.SanitizeName(table);
                string sourceQuery = string.Format("SELECT D.* FROM {0} D", table);
    
                // Define package variables
                this.Variables.Add("sourceQuery", false, "User", sourceQuery);
                this.Variables.Add("tableName", false, "User", table);
    
                // Configure DataFlow properties
                this.DataFlow.Name = "Replicate " + saniName;
                this.DataFlow.Description = "Scripted replication";
    
                // Connection manager configuration
                this.SrcConn.SetConnectionString(sourceServer, database);
                this.SrcConn.Name = "PROD";
                this.SrcConn.Description = string.Empty;
    
                this.DestConn.SetConnectionString(destinationServer, database);
                this.DestConn.Name = "PREPROD";
                this.DestConn.Description = string.Empty;
    
                // Configure Dataflow's Source properties
                this.Source.Name = "Src " + saniName;
                this.Source.Description = string.Empty;
                this.Source.SqlCommand = sourceQuery;
    
                // Configure Dataflow's Destination properties
                this.Dest.Name = "Dest " + saniName;
                this.Dest.Description = string.Empty;
                this.Dest.Table = table;
                this.Dest.FastLoadKeepIdentity = true;
                this.Dest.FastLoadKeepNulls = true;
                this.Dest.DataSourceVariable = this.Variables["tableName"].QualifiedName;
                this.Dest.AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD_VARIABLE;
                this.Dest.LinkAllInputsToOutputs();
            }
    
            /// <summary>
            /// Sanitize a name so that it is valid for SSIS objects. 
            /// Strips []/\:=
            /// Replaces . with _
            /// </summary>
            /// <param name="name"></param>
            /// <returns></returns>
            public static string SanitizeName(string name)
            {
                string saniName = name.Replace("[", String.Empty).Replace("]", string.Empty).Replace(".", "_").Replace("/", string.Empty).Replace("\\", string.Empty).Replace(":", string.Empty);
                return saniName;
            }
        }
    }
    

    
    
    

    调用看起来像 TableToTable S2 =新TableToTable（@localhost\localsqla，AdventureWorks的，[HumanResources] [系]，@localhost\localsqlb）; 和构建一个包，我想要做什么的除了的在源使用变量。

    Invocation looks like TableToTable s2 = new TableToTable(@"localhost\localsqla", "AdventureWorks", "[HumanResources].[Department]", @"localhost\localsqlb"); and that builds a package that does what I want except for using a variable in the source.

    上面的码提供的接入模式为SQL查询和查询嵌入在OLE源。欲望它使用SQL命令从变量和变量被 @ [用户:: sourceQuery] 什么我卡在源使用的是可变的。

    The above code supplies the access mode as SQL Query and the query is embedded in the OLE Source. The desire it to use "SQL Command From Variable" and that variable being @[User::sourceQuery] What I'm stuck on is using a variable in the source.

    它的应的像

            this.Source.DataSourceVariable = this.Variables["sourceQuery"].QualifiedName;
            this.Source.AccessMode = AccessMode.AM_SQLCOMMAND_VARIABLE;
    

    这将导致选择了正确的数据访问模式，但是没有填充的变量。
    

    This results in the correct data access mode selected but the variable isn't populated.

    您可以观察我执行其中的目标类似的一步确实接受变量和作品的权利。

    You can observe that I perform a similar step in the destination which does accept the variable and works "right."

            this.Dest.DataSourceVariable = this.Variables["tableName"].QualifiedName;
            this.Dest.AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD_VARIABLE;
    

    
    
    

    

    清单要把我的排列尝试

            this.Source.AccessMode = AccessMode.AM_OPENROWSET;
    

    
    
    

    结果表或视图和表名或视图是空白的。

    Results in Data Access Mode set to Table or View and name of table or the view is blank.

            this.Source.AccessMode = AccessMode.AM_OPENROWSET_VARIABLE;
    

    在数据访问模式设置结果表或视图名称变量和变量名sourceQuery。非常接近我想要的，除了接入方式是不正确的。是这个包的运行，它会炸掉因为OPENROWSET所期望的直表名。

    Results in Data Access Mode set to "Table or view name variable" and variable name is sourceQuery. Very close to what I want, except the access mode is not correct. Were this package to run, it'd blow up as the OpenRowSet would expect a straight table name.

            this.Source.AccessMode = AccessMode.AM_SQLCOMMAND;
    

    
    
    

    结果为User :: sourceQuery这是变量名称的字面值，这是正确的事情，但由于接入方式是错误的，这是行不通的。

    Results in Data Access Mode set to "SQL Command" and the SQL command text is "User::sourceQuery" That's the literal value of the variable name so it's the right thing but since the access mode is wrong, it doesn't work.

            this.Source.AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD;
            this.Source.AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD_VARIABLE;
    

    
    
    

    Niether这些都是正确的访问模式，因为它们是目标（我还尝试过，但他们没'将不起作用如预期）。

    Niether of these are correct access modes as they are for destinations (I still tried them but they didn't work as expected).

    在这一点上，我想我会尝试通过创建具有定义为我想要的OLE DB源的包向后工作，然后检查源对象的属性。

    At this point, I thought I'd try to work backwards by creating a package that has the OLE DB source defined as I want it and then inspect the source object's properties.

            Application app = new Application();
            Package p = app.LoadPackage(@"C:\sandbox\SSISHackAndSlash\SSISHackAndSlash\EzApiPackage.dtsx", null);
            TableToTable to = new TableToTable(p);
    

    
    
    

    

    我的代码同时设置的SqlCommand和DataSourceVarible与变量的限定名。我推倒变更65381和编译的，希望（一些参考固定在SQL Server 2012中的DLL之后）有可能是一个修复自2008年12月30日的稳定版本，但都无济于事。

    My code has set both SqlCommand and DataSourceVarible with the variable's qualified name. I've pulled down changeset 65381 and compiled that (after fixing some references to the SQL Server 2012 dlls) in hopes there might have been a fix since the Dec 30 2008 Stable build but to no avail.

    我找到他们的代码中的bug或我只是失去了一些东西？

    Have I found a bug in their code or am I just missing something?

    推荐答案

    目前， EzAPI的稳定版本不支持变量作为OLEDB来源财产的分配。我开了一个类似的讨论过的CodePlex上，最终更多地了解如何将所有的工作的。

    The current, stable build of EzAPI does not support the assignment of a variable as an OleDB Source property. I opened a similar discussion over on CodePlex and ended up learning more about how all of this works.

    问题的根源是相关的属性的从可变的SQL命令SqlCommandVariable当接入模式设置为应该设置，目前，代码只覆盖目的地变量

    The root problem is the related property "SqlCommandVariable" should be set when the access mode is set to "SQL Command from Variable." Currently, the code only covers destination variables.

    我的决议是下载源代码并修改二传手在EzComponents.cs（适用于变更65381行1027） DataSourceVariable p>
    
    

    My resolution was to download the source code and modify the setter for the property DataSourceVariable in EzComponents.cs (line 1027 for changeset 65381)

            set 
            { 
                m_comp.SetComponentProperty("OpenRowsetVariable", value); 
                if (AccessMode == AccessMode.AM_SQLCOMMAND_VARIABLE)
                {
                    m_comp.SetComponentProperty("SqlCommandVariable", value); 
                }
                ReinitializeMetaData(); 
            } 
    

    如果你正在寻找得到这个问题的妥善解决，可能会给予好评的问题

    If you're looking to get this problem resolved properly, you may upvote the Issue

    这篇关于什么是EzAPI相当于使用OLE DB源命令将从变量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！