How to programmatically retrieve a list of MS Access tables in an SSIS package?

Question

I inherited a terrible MS Access database that I need to import into SQL. The Access database contains several thousand tables with field definitions that are identical. I have experience with SSIS, and importing a single table is quite simple.

However, I need to create a process in which I can scroll through a list of several thousand table names and import each table. I found this statement that will get a list of all table names in the Access database:

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

However, I'm not sure how to use this (script task syntax?). I would think that I want to do this to populate an object-type SSIS variable. That way, I can use the ForEach loop to cycle through this list of tables and complete the import. How can i do this? Or is there a better way to loop through each table in a database and perform the same process?

I would really appreciate any suggestions. Thanks you!

Answer 1

Here is one possible way to load Access data into SQL Server if all the tables in Access have the same structure. This example will go through tables in Access, namely Country and StateProvince . The package in this example will create these two tables in SQL, if they do not exist, and then populate them with data from Access.

Step by step:

• The Country and StateProvince access tables are shown in screenshots # 1 and # 2 .

• In the SSIS package, create two OLE DB connections to connect to SQL Server and Access, as shown in screenshot # 3 . In addition, create 3 variables as shown in screenshot # 4 . The variables SelectQuery and TableName must be specified by a valid table in Access. This is necessary for the initial configuration of the package. Here, in this case, I selected Country , which exists in Access.

• Select the SelectQuery variable and press F4 to view the property bar. In the Properties panel, set the EvaluateAsExpress property to True and insert the expression "SELECT * FROM " + @[User::TableName] in the Expression property. This expression will be evaluated in the table that is currently in the loop. See Screenshot # 4

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

• Configure SSIS Control Flow tab as shown in screenshot 7 . Put a Script Task and connect it to the Foreach Loop container . Inside the container, place the Execute SQL Task and Data Flow Task .

• Replace the code in the Script Task with the code specified in the <Script Target Group. This code will loop the access scheme and will retrieve only table names. The list of table names is then stored in the AccessTables package AccessTables , which will then be used by the Foreach Loop container .

• In the SQL Server database, create a stored procedure named dbo.CreateTable using the Script provided in the SQL Scripts section. This stored procedure will create a table in SQL Server if it does not already exist. Make sure that you alter the table schema defined in the stored procedure according to your needs.

• Set up 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 the data flow task at this point because tables do not exist in SQL Server. So, we will run the package at this point so that the Access table structures are created in SQL Server. Screenshot # 12 shows an example of a package. Screenshot # 13 shows that the table structures were created in SQL Server, but they are not yet populated with data.

• Now we set up the Data Flow Task . Place the OLE DB Source and OLE DB Destination inside the data flow task. Connect the OLE DB source to the OLE DB destination. See Screenshot # 14 .

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

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

• Screenshot # 19 shows an example run of a package in a Data Flow Task .

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

This example will only work for tables that have the same structure, but differ from the name. If another table named Employees added to Access with only the Id and Name columns. Running this sample package will create the same table in SQL Server and populate it with data.

Hope this helps.

SQL scripts:

 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 

Script Task Code:

C # , which can only be used 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; } } } 

Screenshot # 1:

Screenshot No. 2:

Screenshot 3:

Screenshot 4:

Screenshot No. 5:

Screenshot No. 6:

Screenshot No. 7:

Screenshot # 8:

Screenshot No. 9:

Screenshot No. 10:

Screenshot No. 11:

Screenshot No. 12:

<T411>

Screenshot No. 13:

Screenshot No. 14:

Screenshot No. 15:

Screenshot No. 16:

Screenshot No. 17:

Screenshot No. 18:

Screenshot # 19:

Screenshot No. 20:

Answer 2

You can put the results of the sql task in an object variable variable. This variable will be available to you for use in the loop task.

Inside the for loop, you can change the name of the table you are working with using expressions .

After a quick review, this article can detail the first part of the process:

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

Answer 3

As someone who has access domains, I first correct the data in Access (i.e. by combining several tables into master tables) and then using the SQL Server Migration Assistant to access upsize. This allows you to simulate imports and fix any problems before you do so.

The first step is to merge the data tables that I just copied into VBA, although I probably have to create tables with metadata that display what is imported into which (if the tables do not use naming conventions that allow algorithmically determined).