如何以编程方式获取 SSIS 包中的 MS Access 表列表?
我继承了一个非常编写的 MS Access 数据库,我需要将其导入到 SQL 中。 Access 数据库中有数千个表,其字段定义相同。我对 SSIS 有一些经验,导入一张表非常简单。
但是,我需要创建一个过程,可以在其中循环遍历数千个表名的列表并导入每个表。我发现这个语句,它将获取 Access 数据库中所有表名的列表:
从 MSysObjects WHERE (((MSysObjects.Type)=1) AND ((Left([Name],4))<>"MSys")) 中选择名称;
但是,我不确定如何使用它(脚本任务语法?)。我想我会想要这样做来填充“对象”类型的 SSIS 变量。这样,我可以使用 ForEach 循环循环访问此表列表并执行导入。我该怎么做?或者是否有更好的方法来循环访问数据库中的每个表并执行相同的过程?
我将非常感谢任何建议。感谢您!
I have inherited a terribly written MS Access database that I need to import into SQL. The Access database has several thousand tables in it with field definitions that are identical. I have some experience with SSIS, and importing one table is pretty simple.
However, I need to create a process where I can loop through the list of several thousand table names and import each table. I found this statement, that will get a list of all the table names in an Access database:
SELECT Name FROM MSysObjects WHERE (((MSysObjects.Type)=1) AND ((Left([Name],4))<>"MSys")) ;
However, I am unsure of how to use this (script task syntax?). I would think I would want to do this to populate a SSIS variable of an "object" type. That way, I can use a ForEach Loop to cycle through this list of tables and perform the importing. How can I do this? Or is there a better way to cycle through each table in the database and perform the same process?
I would greatly appreciate any suggestions. Thanks you!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
只要 Access 中的所有表都具有相同的结构,您就可以通过以下一种方法将 Access 数据加载到 SQL Server 中。此示例将循环访问 Access 中的表,即
Country
和StateProvince
。如果这两个表不存在,本示例中的包将创建这两个表,然后使用 Access 中的数据填充它们。分步过程:
访问表
Country
和StateProvince
如屏幕截图#1所示#2.在 SSIS 包上,创建两个 OLE DB 连接以连接到 SQL Server 和 Access,如屏幕截图 #3 所示。另外,创建 3 个变量,如屏幕截图 #4 所示。变量
SelectQuery
和TableName
应由 Access 中的有效表指定。这是包的初始配置所必需的。在本例中,我选择了Country
,它确实存在于 Access 中。选择变量
SelectQuery
并按F4查看属性窗格。在“属性”窗格中,将属性EvaluateAsExpress
设置为 True 并粘贴表达式"SELECT * FROM " + @[User::TableName]Expression
属性中的 code>。该表达式将计算当前正在循环的表。请参阅屏幕截图 #4屏幕截图 #5 和 #6 显示表
dbo.Country
和 < code>dbo.StateProvince 在 SQL Server 中不存在。配置 SSIS 包的
控制流
选项卡,如屏幕截图 #7 所示。放置一个脚本任务
并将其连接到Foreach循环容器
。在容器内,放置一个执行 SQL 任务
和一个数据流任务
。将脚本任务中的代码替换为脚本任务代码部分中给出的代码。此代码将循环 Access 架构并仅获取表名称。然后,表名称列表存储在包变量
AccessTables
中,然后由Foreach 循环容器
使用。在 SQL Server 数据库中,使用 SQL 脚本 部分下提供的脚本创建一个名为
dbo.CreateTable
的存储过程。该存储过程将在 SQL Server 中创建一个表(如果该表尚不存在)。确保根据您的需要更改存储过程中定义的表架构。
配置
Foreach 循环容器
,如屏幕截图所示 #8< /strong> 和 #9。配置执行 SQL 任务,如屏幕截图 #10 和 #11 所示。
此时我们无法配置数据流任务,因为 SQL Server 中不存在这些表。因此,我们此时将执行该包,以便在 SQL Server 中创建 Access 表结构。屏幕截图 #12 显示示例包执行情况。屏幕截图 #13 显示表结构已在 SQL Server 中创建,但尚未填充数据。
现在,我们将配置
数据流任务
。将OLE DB Source
和OLE DB Destination
放置在数据流任务内。将 OLE DB 源连接到 OLE DB 目标。请参阅屏幕截图 #14。配置
OLE DB 源
,如屏幕截图 #15 和 #16 所示。配置
OLE DB 目标
,如屏幕截图 #17 和 #18 所示。屏幕截图 #19 显示
Data Flow Task
中的示例包执行情况。屏幕截图 #20 显示 SQL Server 表现在填充了 Access 表中的数据。
此示例仅适用于具有相同结构但名称不同的表。如果将另一个名为
Employees
的表添加到 Access 中,其中仅包含Id
和Name
列。执行此示例包将在 SQL Server 中创建相同的表,并用数据填充该表。希望有帮助。
SQL 脚本:
脚本任务代码:
C# 代码,只能在
SSIS 2008 及更高版本
中使用。屏幕截图 #1:
屏幕截图 #2:
屏幕截图 #3:
屏幕截图 #4:
屏幕截图 #5:< /strong>
屏幕截图#6:
屏幕截图 #7:
屏幕截图 #8:< /strong>
屏幕截图#9:
屏幕截图 #10:
屏幕截图 #11:
屏幕截图#12:
屏幕截图 #13:
屏幕截图 #14:
屏幕截图 #15:
屏幕截图 #16:
屏幕截图 #17:
屏幕截图 #18:
屏幕截图 #19:
屏幕截图#20:
Here is one possible way that you can achieve loading Access data into SQL Server as long as all the tables in Access have the same structure. This example will loop through tables in Access namely
Country
andStateProvince
. The package in this example will create these two tables in SQL if they don't exist and then populate them with data from Access.Step-by-step process:
Access tables
Country
andStateProvince
are shown in screenshots #1 and #2.On the SSIS package, create two OLE DB Connections to connect to SQL Server and Access as shown in screenshot #3. Also, create 3 variables as shown in screenshot #4. Variables
SelectQuery
andTableName
should be specified by a valid table in Access. This is needed for initial configuration of the package. Here in this case, I have chosenCountry
, which does exist in Access.Select the variable
SelectQuery
and press F4 to view the properties pane. On the Properties pane, set the propertyEvaluateAsExpress
to True and paste the expression"SELECT * FROM " + @[User::TableName]
in theExpression
property. This expression will evaluate to the table that is currently being looped through. Refer screenshot #4Screenshots #5 and #6 show that the tables
dbo.Country
anddbo.StateProvince
do not exist in SQL Server.Configure the
Control Flow
tab of the SSIS package as shown in screenshot #7. Place aScript Task
and connect it to aForeach Loop container
. Within the container, place anExecute SQL Task
and aData Flow Task
.Replace the code in the Script Task with the code given under the Script Task Code section. This code will loop the Access schema and will fetch only the table names. The list of table names are then stored in the package variable
AccessTables
, which will then used byForeach loop container
.In the SQL Server database create a stored procedure named
dbo.CreateTable
using the script provided under SQL Scripts Section. This stored procedure will create a table in the SQL Server if it didn't already exist.Make sure that you alter the table schema defined in the stored procedure according to your needs.
Configure the
Foreach loop container
as shown in screenshots #8 and #9.Configure the Execute SQL Task as shown in screenshots #10 and #11.
We cannot configure Data Flow Task at this point because the tables don't exist in SQL Server. So, we will execute the package at this point so the Access table structures are created in the SQL Server. Screenshot #12 shows sample package execution. Screenshot #13 shows that the table structures have been created in SQL Server but they are not yet populated with data.
Now, we will configure the
Data Flow Task
. Place anOLE DB Source
andOLE DB Destination
inside the Data Flow Task. Connect the OLE DB Source to OLE DB Destination. Refer screenshot #14.Configure the
OLE DB Source
as shown in screenshots #15 and #16.Configure the
OLE DB Destination
as shown in screenshots #17 and #18.Screenshot #19 shows sample package execution within
Data Flow Task
.Screenshot #20 shows that SQL Server tables are now populated with data from Access tables.
This example will work only for tables having the same structure but differing in the name. If another table named
Employees
are added to the Access with only columnsId
andName
. Executing this example package will create the same table in SQL Server and will also populate it with the data.Hope that helps.
SQL Scripts:
Script Task Code:
C# code that can be used only in
SSIS 2008 and above
.Screenshot #1:
Screenshot #2:
Screenshot #3:
Screenshot #4:
Screenshot #5:
Screenshot #6:
Screenshot #7:
Screenshot #8:
Screenshot #9:
Screenshot #10:
Screenshot #11:
Screenshot #12:
Screenshot #13:
Screenshot #14:
Screenshot #15:
Screenshot #16:
Screenshot #17:
Screenshot #18:
Screenshot #19:
Screenshot #20:
您可以将 sql 任务的结果放入对象变量类型的变量中。然后该变量将可供您在循环任务中使用。
在 for 循环中,您可以使用表达式修改正在操作的表名称。
快速浏览一下后,本文可能会详细介绍该过程的第一部分:
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64014/
You could put the results of a sql task into a variable of type object variable. That variable will then be available for you to use in a loop task.
Inside your for loop you could modify the table name you are operating on using expressions.
After a quick glance through, this article may detail the first part of the process:
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64014/
作为具有 Access 技术的人,我会首先修复 Access 中的数据(即,将多个表合并到主表中),然后使用 SQL Server Migration Assistant for Access 进行扩容。它允许您在实际执行之前模拟导入并纠正任何问题。
合并数据表的第一步我只是在 VBA 中编写代码,尽管我可能必须创建一些带有元数据的表,这些元数据映射导入的内容(除非表使用允许通过算法确定的命名约定) )。
As somebody with Access chops, I'd fix the data in Access first (i.e., merging the multiple tables into master tables) then use the SQL Server Migration Assistant for Access to upsize. It allows you to simulate the import and correct any issues before you actually do it.
The first step of merging the data tables I'd just code up in VBA, though I'd probably have to create some tables with metadata that map what gets imported into what (unless the tables use naming conventions that allow this to be determined algorithmically).