1. 首页
  2. 其他

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

本文介绍了如何以编程方式获取 SSIS 包中的 MS Access 表列表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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.

但是,我需要创建一个进程,我可以在其中循环遍历数千个表名的列表并导入每个表.我找到了这个语句,它将获得 Access 数据库中所有表名的列表:

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”)) ;

SELECT Name FROM MSysObjects WHERE (((MSysObjects.Type)=1) AND ((Left([Name],4))<>”MSys”)) ;

但是,我不确定如何使用它(脚本任务语法?).我想我会想要这样做来填充对象”类型的 SSIS 变量.这样,我可以使用 ForEach 循环循环浏览此表列表并执行导入.我怎样才能做到这一点?或者有没有更好的方法来循环遍历数据库中的每个表并执行相同的过程?

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!

推荐答案

只要 Access 中的所有表具有相同的结构,就可以实现将 Access 数据加载到 SQL Server 中的一种可能方法.此示例将遍历 Access 中的表,即 CountryStateProvince.如果这两个表不存在,本示例中的包将在 SQL 中创建这两个表,然后用 Access 中的数据填充它们.

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 and StateProvince. 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.

分步过程:

  1. 访问表CountryStateProvince 显示在屏幕截图#1 和#2 中.

  1. Access tables Country and StateProvince are shown in screenshots #1 and #2.

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

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 and TableName should be specified by a valid table in Access. This is needed for initial configuration of the package. Here in this case, I have chosen Country, which does exist in Access.

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

Select the variable SelectQuery and press F4 to view the properties pane. On the Properties pane, set the property EvaluateAsExpress to True and paste the expression "SELECT * FROM " + @[User::TableName] in the Expression property. This expression will evaluate to the table that is currently being looped through. Refer screenshot #4

屏幕截图 #5 和 #6 显示表 dbo.Countrydbo.StateProvince> 在 SQL Server 中不存在.

Screenshots #5 and #6 show that the tables dbo.Country and dbo.StateProvince do not exist in SQL Server.

配置 SSIS 包的 Control Flow 选项卡,如屏幕截图 #7 所示.放置一个 Script Task 并将其连接到 Foreach Loop 容器.在容器内,放置一个 Execute SQL Task 和一个 Data Flow Task.

Configure the Control Flow tab of the SSIS package as shown in screenshot #7. Place a Script Task and connect it to a Foreach Loop container. Within the container, place an Execute SQL Task and a Data Flow Task.

用脚本任务代码部分下给出的代码替换脚本任务中的代码.此代码将循环访问模式并仅获取表名.然后将表名列表存储在包变量 AccessTables 中,然后由 Foreach 循环容器 使用.

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 by Foreach loop container.

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

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.

配置 Foreach 循环容器,如屏幕截图 #8 和 #9 所示.

Configure the Foreach loop container as shown in screenshots #8 and #9.

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

Configure the Execute SQL Task as shown in screenshots #10 and #11.

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

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.

现在,我们将配置数据流任务.在数据流任务中放置一个 OLE DB SourceOLE DB Destination.将 OLE DB 源连接到 OLE DB 目标.请参阅屏幕截图 #14.

Now, we will configure the Data Flow Task. Place an OLE DB Source and OLE DB Destination inside the Data Flow Task. Connect the OLE DB Source to OLE DB Destination. Refer screenshot #14.

配置 OLE DB Source,如屏幕截图 #15 和 #16 所示.

Configure the OLE DB Source as shown in screenshots #15 and #16.

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

Configure the OLE DB Destination as shown in screenshots #17 and #18.

屏幕截图 #19 显示了 Data Flow Task 中的示例包执行.

Screenshot #19 shows sample package execution within Data Flow Task.

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

Screenshot #20 shows that SQL Server tables are now populated with data from Access tables.

此示例仅适用于具有相同结构但名称不同的表.如果另一个名为 Employees 的表被添加到 Access 中,只有列 IdName.执行此示例程序包将在 SQL Server 中创建相同的表,并将用数据填充它.

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 columns Id and Name. Executing this example package will create the same table in SQL Server and will also populate it with the data.

希望有所帮助.

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 及更高版本中使用.

C# code that can be used only in SSIS 2008 and above.

/*
   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:

截图 #6:

屏幕截图 #7:

截图 #8:

屏幕截图 #9:

屏幕截图 #10:

屏幕截图 #11:

屏幕截图 #12:

屏幕截图 #13:

屏幕截图 #14:

屏幕截图 #15:

屏幕截图 #16:

屏幕截图 #17:

屏幕截图 #18:

屏幕截图 #19:

屏幕截图 #20:

这篇关于如何以编程方式获取 SSIS 包中的 MS Access 表列表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,WP2

如何以编程方式获取 SSIS 包中的 MS Access 表列表? 为WP2原创文章,链接:https://www.wp2.cn/other/%e5%a6%82%e4%bd%95%e4%bb%a5%e7%bc%96%e7%a8%8b%e6%96%b9%e5%bc%8f%e8%8e%b7%e5%8f%96-ssis-%e5%8c%85%e4%b8%ad%e7%9a%84-ms-access-%e8%a1%a8%e5%88%97%e8%a1%a8/