使用脚本任务创建ADO.NET(ODBC)数据流源

Question

我需要一些关于动态创建包的SSIS脚本任务（SQL 2008 R2）的帮助。我正在完善一个包，该包将数据从Sage Timberline（现在更名为Sage 300）普及SQL环境复制到SQL服务器数据仓库。我可以创建一个包，打开与Timberline的连接，并将数据复制到SQL Server中的表中。问题是，对于timberline中的每个公司和SQL中的每个表，我需要创建一个单独的数据流任务。考虑到Timberline公司的三个文件夹以及每个文件夹中的表数量，这将花费大量时间来创建，并且维护和故障排除也很麻烦。

我正在尝试创建一个包，该包使用Foreach循环来创建一个创建ADO/ODBC源（Timberline）、OLE目标（SQL）并动态处理列映射的包。我在这里找到了几乎可以满足我需要的代码。

我测试了这段代码，使用OLE SQL源代码和目标代码效果很好。这个脚本之所以有效，是因为它动态地处理列映射。因此，如果您将它放入一个由大约100个表组成的Foreach循环中，每个循环都可以动态创建数据流并映射列，然后执行新的包。

我的问题是，我只能使用ODBC连接到Timberline。因此，我需要修改脚本以使用ADO.NET（ODBC）而不是OLE创建源连接。我想弄清楚这件事时遇到了很多麻烦。有人能帮我一下吗？

这里是我首先尝试的其他几件事，除了这种方法：

解决方案：设置链接服务器到Timberline Pervasive SQL
问题：SQL服务器是64位的，Timberline驱动程序是32位的。使用链接服务器会返回体系结构不匹配错误。我打电话给Sage，他们说他们没有发布64位硬盘的计划。

解决方案：使用其中一个SQL传输任务
问题：仅适用于SQL数据库。此源是一个普适SQL数据库

解决方案：使用"INSERT…INTO…"类型的脚本
问题：这需要一个链接服务器。参见上面的问题

以下是我需要帮助的VB.NET原始代码部分：

'To Create a package named [Sample Package]
Dim package As New Package()
package.Name = "Sample Package"
package.PackageType = DTSPackageType.DTSDesigner100
package.VersionBuild = 1
'To add Connection Manager to the package
'For source database (OLTP)
Dim OLTP As ConnectionManager = package.Connections.Add("OLEDB")
OLTP.ConnectionString = "Data Source=.;Initial Catalog=OLTP;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
OLTP.Name = "LocalHost.OLTP"
'To add Load Employee Dim to the package [Data Flow Task]
Dim dataFlowTaskHost As TaskHost = DirectCast(package.Executables.Add("SSIS.Pipeline.2"), TaskHost)
dataFlowTaskHost.Name = "Load Employee Dim"
dataFlowTaskHost.FailPackageOnFailure = True
dataFlowTaskHost.FailParentOnFailure = True
dataFlowTaskHost.DelayValidation = False
dataFlowTaskHost.Description = "Data Flow Task"
'-----------Data Flow Inner component starts----------------
Dim dataFlowTask As MainPipe = TryCast(dataFlowTaskHost.InnerObject, MainPipe)
' Source OLE DB connection manager to the package.
Dim SconMgr As ConnectionManager = package.Connections("LocalHost.OLTP")
' Create and configure an OLE DB source component.
Dim source As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]()
source.ComponentClassID = "DTSAdapter.OLEDBSource.2"
' Create the design-time instance of the source.
Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate()
' The ProvideComponentProperties method creates a default output.
srcDesignTime.ProvideComponentProperties()
source.Name = "Employee Dim from OLTP"
' Assign the connection manager.
source.RuntimeConnectionCollection(0).ConnectionManagerID = SconMgr.ID
source.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(SconMgr)
' Set the custom properties of the source.
srcDesignTime.SetComponentProperty("AccessMode", 0)
' Mode 0 : OpenRowset / Table - View
srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Employee_Dim]")
' Connect to the data source, and then update the metadata for the source.
srcDesignTime.AcquireConnections(Nothing)
srcDesignTime.ReinitializeMetaData()
srcDesignTime.ReleaseConnections()

提前感谢！

Answer 1

如果您需要Source和Destination之间的Derived Column转换，这里的C#代码就是您所需要的。。。

http://bifuture.blogspot.com/2011/01/ssis-adding-derived-column-to-ssis.html

要获取源&目的地连接正常工作，这里有一些秘密酱汁可以在COM和.Net之间工作。。。http://blogs.msdn.com/b/mattm/archive/2008/12/30/api-sample-ado-net-source.aspx还有一个类似的页面显示了如何处理OleDB连接。

创建源表很容易。应使用GetSchema（"MetaDataCollections"）检索可访问的可用ODBC元数据集合。这将返回可用于特定ODBC驱动程序的可用架构集合的列表。接下来，您将希望看到从GetSchema返回的数据类型（"DataTypes"），这样您就可以正确地解释从GetSchema检索到的每列的数据类型，以使您的SQL Server创建表脚本（我假设您已经完成了）。为了至少弄清楚哪些表具有主键，您需要遍历从GetSchema（"tables"）返回的每个表，以便使用GetSchema（"Indexes"）。有一个错误要求您一次查询一个表的索引。谷歌搜索这个很容易-创建一个字符串数组作为第三个参数传入：GetSchema（"Indexes"，tblName，resultArray[]）

我所做的是将Tables和Columns集合放入父SSIS包中的对象变量中。因为Timberline太快了（不是），所以把所有的柱子都拉下来并在本地过滤似乎更有效。。。如果需要，我会在SQL Server中创建表。

完成后，在"设计模式"下，再次使用表的本地副本在脚本任务中操作SSIS包（更改源表和目标表，并重做列映射），并执行内存中的SSIS包。

对我来说，这需要一段时间才能弄清楚。以上两个URL都是必需的。我找到并复制了.Net 2.0 Dts.PipelineWrap和Dts.RuntimeWrappe.dlls到Microsoft.Net\FrameworkV2.0xxxxx文件夹，然后在每个想要使用它们的脚本任务中引用它们，然后设置我的"使用DtsPW=Microsoft.SqlServer.Dts.Pipeine.Wrapper"等

值得注意的是，因为Timberline是32位ODBC，所以我认为有必要构建SSIS包以使用"X86"，并将脚本任务定位为使用.Net 2.0框架。

我使用派生列代码是因为我需要将多个TimberlineDB复制到一个SQLServerDB中。派生列将"CompanyID"值添加到SQL Server的输出管道中。

最后，根据目的地所连接的管道，将目的地的虚拟输入列映射到其外部元数据列：

      foreach (DtsPW.IDTSVirtualInputColumn100 vColumn in destVirtInput.VirtualInputColumnCollection)
            {
                var vCol = destInst.SetUsageType(destInput.ID, destVirtInput, vColumn.LineageID, DtsPW.DTSUsageType.UT_READWRITE);
                destInst.MapInputColumn(destInput.ID, vCol.ID, destInput.ExternalMetadataColumnCollection[vColumn.Name].ID);
            }

无论如何，该代码在bifuture.blogspot.com页面的上下文中会更有意义。

EzApi库也可以帮助实现这一点，但它的AdoNet连接源被编码为一个虚拟类，因此您需要实现特定的类来使用。我的C功夫还不够强。。。

此外，CozyRoc还销售了一个带有自定义SSIS控件（数据流Source和Destination控件…）的工具集，看起来它也可以在动态输入到输出列映射中执行此操作。

我的包裹现在似乎足够好了。。。哦，还有一点，我在尝试使用到Timberline的无DSN ODBC连接时运气不佳，只是：DSN=dsnname；Uid=用户；Pwd=Pwd；

在64位平台上运行的SSIS包在64位操作系统上看不到32位DSN，似乎。。。至少，它对我不起作用（win7-64，32位文本ODBC DSN）。