如何以编程方式获取 SSIS 包中的 MS Access 表列表？

Question

我继承了一个非常编写的 MS Access 数据库，我需要将其导入到 SQL 中。 Access 数据库中有数千个表，其字段定义相同。我对 SSIS 有一些经验，导入一张表非常简单。

但是，我需要创建一个过程，可以在其中循环遍历数千个表名的列表并导入每个表。我发现这个语句，它将获取 Access 数据库中所有表名的列表：

从 MSysObjects WHERE (((MSysObjects.Type)=1) AND ((Left([Name],4))<>"MSys")) 中选择名称；

但是，我不确定如何使用它（脚本任务语法？）。我想我会想要这样做来填充“对象”类型的 SSIS 变量。这样，我可以使用 ForEach 循环循环访问此表列表并执行导入。我该怎么做？或者是否有更好的方法来循环访问数据库中的每个表并执行相同的过程？

我将非常感谢任何建议。感谢您！

Answer 1

只要 Access 中的所有表都具有相同的结构，您就可以通过以下一种方法将 Access 数据加载到 SQL Server 中。此示例将循环访问 Access 中的表，即 Country 和 StateProvince。如果这两个表不存在，本示例中的包将创建这两个表，然后使用 Access 中的数据填充它们。

分步过程：

1. 访问表Country和StateProvince如屏幕截图#1所示#2.

2. 在 SSIS 包上，创建两个 OLE DB 连接以连接到 SQL Server 和 Access，如屏幕截图 #3 所示。另外，创建 3 个变量，如屏幕截图 #4 所示。变量 SelectQuery 和 TableName 应由 Access 中的有效表指定。这是包的初始配置所必需的。在本例中，我选择了 Country，它确实存在于 Access 中。

3. 选择变量SelectQuery并按F4查看属性窗格。在“属性”窗格中，将属性 EvaluateAsExpress 设置为 True 并粘贴表达式 "SELECT * FROM " + @[User::TableName]Expression 属性中的 code>。该表达式将计算当前正在循环的表。请参阅屏幕截图 #4

4. 屏幕截图 #5 和 #6 显示表 dbo.Country 和 < code>dbo.StateProvince 在 SQL Server 中不存在。

5. 配置 SSIS 包的控制流选项卡，如屏幕截图 #7 所示。放置一个脚本任务并将其连接到Foreach循环容器。在容器内，放置一个执行 SQL 任务 和一个数据流任务。

6. 将脚本任务中的代码替换为脚本任务代码部分中给出的代码。此代码将循环 Access 架构并仅获取表名称。然后，表名称列表存储在包变量 AccessTables 中，然后由 Foreach 循环容器 使用。

7. 在 SQL Server 数据库中，使用 SQL 脚本 部分下提供的脚本创建一个名为 dbo.CreateTable 的存储过程。该存储过程将在 SQL Server 中创建一个表（如果该表尚不存在）。 确保根据您的需要更改存储过程中定义的表架构。

8. 配置 Foreach 循环容器，如屏幕截图所示 #8< /strong> 和 #9。

9. 配置执行 SQL 任务，如屏幕截图 #10 和 #11 所示。

10. 此时我们无法配置数据流任务，因为 SQL Server 中不存在这些表。因此，我们此时将执行该包，以便在 SQL Server 中创建 Access 表结构。屏幕截图 #12 显示示例包执行情况。屏幕截图 #13 显示表结构已在 SQL Server 中创建，但尚未填充数据。

11. 现在，我们将配置数据流任务。将 OLE DB Source 和 OLE DB Destination 放置在数据流任务内。将 OLE DB 源连接到 OLE DB 目标。请参阅屏幕截图 #14。

12. 配置 OLE DB 源，如屏幕截图 #15 和 #16 所示。

13. 配置 OLE DB 目标，如屏幕截图 #17 和 #18 所示。

14. 屏幕截图 #19 显示 Data Flow Task 中的示例包执行情况。

15. 屏幕截图 #20 显示 SQL Server 表现在填充了 Access 表中的数据。

此示例仅适用于具有相同结构但名称不同的表。如果将另一个名为 Employees 的表添加到 Access 中，其中仅包含 Id 和 Name 列。执行此示例包将在 SQL Server 中创建相同的表，并用数据填充该表。

希望有帮助。

SQL 脚本：

CREATE PROCEDURE [dbo].[CreateTable]
(
    @TableName  VARCHAR(255)
)
AS
BEGIN

    SET NOCOUNT ON

    DECLARE @SQL VARCHAR(MAX)

    SET @SQL = 'IF NOT EXISTS ( SELECT  * 
                                FROM    sys.objects 
                                WHERE   object_id = OBJECT_ID(N''[dbo].' + @TableName + ''') 
                                AND     type in (N''U''))
                    CREATE TABLE [dbo].' + @TableName + '(
                        [ID] [int] NOT NULL,
                        [Name] [nvarchar](255) NULL
                        ) ON [PRIMARY]'

    EXEC (@SQL)
END
GO

脚本任务代码：

C# 代码，只能在 SSIS 2008 及更高版本中使用。

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_9b2714c55db14556be74ca92f345c4e3.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        public void Main()
        {
            Variables varCollection = null;
            DataTable schemaTables = null;
            ArrayList tableNames = new ArrayList();

            Dts.VariableDispenser.LockForWrite("User::AccessTables");
            Dts.VariableDispenser.GetVariables(ref varCollection);

            using (OleDbConnection connection = new OleDbConnection(Dts.Connections["AccessDB"].ConnectionString.ToString()))
            {
                string[] restrictions = new string[4];
                restrictions[3] = "Table";    
                connection.Open();
                schemaTables = connection.GetSchema("Tables", restrictions);
            }

            foreach (DataRow row in schemaTables.Rows)
            {
                foreach (DataColumn column in schemaTables.Columns)
                {
                    if (column.ColumnName.ToUpper() == "TABLE_NAME")
                    {
                        tableNames.Add(row[column].ToString());
                    }
                }
            }

            varCollection["User::AccessTables"].Value = tableNames;

            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

屏幕截图 #1：

屏幕截图 #2：

屏幕截图 #3：

屏幕截图 #4：

屏幕截图 #5：< /strong>

屏幕截图#6：

屏幕截图 #7：

屏幕截图 #8：< /strong>

屏幕截图#9：

屏幕截图 #10：

屏幕截图 #11：

屏幕截图#12:

屏幕截图 #13:

屏幕截图 #14:

屏幕截图 #15：

屏幕截图 #16：

屏幕截图 #17：

屏幕截图 #18：

屏幕截图 #19：

屏幕截图＃20：

Answer 2

您可以将 sql 任务的结果放入对象变量类型的变量中。然后该变量将可供您在循环任务中使用。

在 for 循环中，您可以使用表达式修改正在操作的表名称。

快速浏览一下后，本文可能会详细介绍该过程的第一部分：

http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/64014/

Answer 3

作为具有 Access 技术的人，我会首先修复 Access 中的数据（即，将多个表合并到主表中），然后使用 SQL Server Migration Assistant for Access 进行扩容。它允许您在实际执行之前模拟导入并纠正任何问题。

合并数据表的第一步我只是在 VBA 中编写代码，尽管我可能必须创建一些带有元数据的表，这些元数据映射导入的内容（除非表使用允许通过算法确定的命名约定） ）。