tl;dr
使用“来自变量的 SQL 命令”数据访问模式的 OLE DB 源并分配变量的 EzAPI 代码是什么?
Preamble
每月一次,我们需要使用生产数据的子集刷新我们的公共测试站点。我们已确定,根据我们的需求,SSIS 解决方案最适合完成此任务。
我的目标是系统地构建大量(100+)“复制”包。EzAPI http://sqlsrvintegrationsrv.codeplex.com/releases/view/21238是一个友好的包装SSIS对象模型 http://msdn.microsoft.com/en-us/library/ms198518.aspx这似乎是节省鼠标点击的好方法。
我希望我的包裹看起来像
- 变量-“表名”; [架构].[表名]
- 变量-“sourceQuery”; SELECT * FROM [架构].[表名]
- 数据流 - “复制 Schema_TableName”
- OLE DB 源 - “Src Schema_TableName”;数据访问方式:来自变量的SQL命令;变量名称:
User::sourceQuery
- OLE DB 目标 - “Dest Schema_TableName”;表或视图名称变量-快速加载;变量名称 - User::tableName
Code
这是我的表到表复制包的代码。
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 = new TableToTable(@"localhost\localsqla", "AdventureWorks", "[HumanResources].[Department]", @"localhost\localsqlb");
这构建了一个可以完成我想要的功能的包except用于在源中使用变量。
Problem
上面的代码提供了 SQL 查询的访问模式,并且查询嵌入在 OLE 源中。希望使用“来自变量的 SQL 命令”并且该变量是@[User::sourceQuery]
我所坚持的是在源代码中使用变量。
It should是一个简单的分配问题
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.
![ole db source](https://i.stack.imgur.com/tBvyQ.png)
您可以观察到我在目标中执行了类似的步骤does接受变量并“正确”工作。
this.Dest.DataSourceVariable = this.Variables["tableName"].QualifiedName;
this.Dest.AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD_VARIABLE;
什么不起作用
列出我尝试过的排列
this.Source.AccessMode = AccessMode.AM_OPENROWSET;
结果数据访问模式设置为表或视图,并且表或视图的名称为空。
this.Source.AccessMode = AccessMode.AM_OPENROWSET_VARIABLE;
数据访问模式中的结果设置为“表或视图名称变量”,变量名称为 sourceQuery。非常接近我想要的,只是访问模式不正确。如果这个包运行,它会崩溃,因为 OpenRowSet 需要一个直接的表名称。
this.Source.AccessMode = AccessMode.AM_SQLCOMMAND;
结果数据访问模式设置为“SQL 命令”,并且 SQL 命令文本为“User::sourceQuery” 这是变量名称的字面值,因此它是正确的,但由于访问模式错误,因此它不起作用。
this.Source.AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD;
this.Source.AccessMode = AccessMode.AM_OPENROWSET_FASTLOAD_VARIABLE;
这些都不是正确的访问模式,因为它们适用于目的地(我仍然尝试过它们,但它们没有按预期工作)。
此时,我想我应该尝试逆向工作,创建一个包,其中定义了我想要的 OLE DB 源,然后检查源对象的属性。
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 日稳定版本以来可能有修复,但没有成功。
我是否在他们的代码中发现了错误,或者我只是遗漏了一些东西?