如何以编程方式获取SSIS包中的MS Access表列表? [英] How do I programmatically get the list of MS Access tables within an SSIS package?
问题描述
我继承了一个非常编写的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:
从MSysObjects中选择名称((((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中的表Country和StateProvince.如果此示例中的包不存在，则会在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.
分步过程:
-
访问表
Country和StateProvince显示在屏幕快照# 1 和# 2 中.
Access tables
CountryandStateProvinceare shown in screenshots #1 and #2.
在SSIS包上，创建两个OLE DB连接以连接到SQL Server和Access，如屏幕快照# 3 中所示.另外，创建3个变量，如屏幕快照# 4 中所示.变量SelectQuery和TableName应该由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属性中.该表达式将求值到当前正在循环通过的表.请参阅屏幕快照# 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.Country和dbo.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 container.在容器内，放置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 loop container使用.
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中创建一个表. Make sure that you alter the table schema defined in the stored procedure according to your needs.
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 loop container，如屏幕快照# 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.
现在，我们将配置Data Flow Task.在数据流任务中放置一个OLE DB Source和OLE 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 Destination，如屏幕快照# 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 中，且仅包含列Id和Name.执行此示例程序包将在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 and above 中使用.
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表列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
