How do I programmatically get the list of MS Access tables within an SSIS package?

Question

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!

Contravene

Answer 1

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.

Step-by-step process:

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

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

3. 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

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

5. 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.

6. 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.

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

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

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

10. 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.

11. 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.

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

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

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

15. 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 columns Id and Name. 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:

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# 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;
        }
    }
}

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:

Natiha

Siva, I will have to say: that was the most comprehensive and well written response I have EVER received. Using your examples and screenshots, I was able to create my package. I had to make a minor change here or there to handle my specific database, but your writeup made it very understandable. THANK YOU very much for your time in writting all of this up! You really bailed me out of jam. – Contravene

This is excellent information! Has anyone adapted this to work in a ForEach loop that needs to load all access databases in a directory? I did a simple test and it looks like it won't work unless I'm able to close the OleDB connection before changing the ConnectionString that specifies the next Access database to load. Any thoughts? – Entopic

@Siva, can you please elaborate what you mean by as long as all the tables in Access have the same structure ? – Indicatory

Answer 2

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/

Presidency

Answer 3

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).

Photocopier