Как программно получить список таблиц MS Access в пакете служб SSIS?

Я унаследовал ужасно написанную базу данных MS Access, которую мне нужно импортировать в SQL. База данных Access содержит несколько тысяч таблиц с одинаковыми определениями полей. У меня есть некоторый опыт работы с SSIS, и импортировать одну таблицу довольно просто.

Однако мне нужно создать процесс, в котором я мог бы перебрать список из нескольких тысяч имен таблиц и импортировать каждую таблицу. Я нашел это утверждение, которое получит список всех имен таблиц в базе данных Access:

ВЫБРАТЬ Имя ОТ MSysObjects WHERE (((MSysObjects.Type)=1) И ((Слева ([Имя],4))<>"MSys"));

Однако я не уверен, как это использовать (синтаксис задачи скрипта?). Я думаю, я бы хотел сделать это, чтобы заполнить переменную SSIS типа "объект". Таким образом, я могу использовать цикл ForEach для циклического перемещения по этому списку таблиц и выполнения импорта. Как я могу это сделать? Или есть лучший способ циклически проходить по каждой таблице в базе данных и выполнять один и тот же процесс?

Буду очень признателен за любые предложения. Спасибо вам!

Источник

3 ответа

Решение

Вот один из возможных способов загрузки данных Access в SQL Server, если все таблицы в Access имеют одинаковую структуру. Этот пример будет проходить по таблицам в Access, а именно Country а также StateProvince, Пакет в этом примере создаст эти две таблицы в SQL, если они не существуют, а затем заполнит их данными из 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 имущество. Это выражение будет соответствовать таблице, которая в данный момент проходит через. Смотрите скриншот №4

  4. Скриншоты № 5 и № 6 показывают, что таблицы dbo.Country а также dbo.StateProvince не существует в SQL Server.

  5. Настройте Control Flow вкладка пакета служб SSIS, как показано на скриншоте # 7. Поместите Script Task и подключить его к Foreach Loop container, Внутри контейнера поместите Execute SQL Task и Data Flow Task,

  6. Замените код в Задаче сценария на код, указанный в разделе "Код задачи сценария". Этот код будет зацикливать схему доступа и извлекать только имена таблиц. Список имен таблиц затем сохраняется в переменной пакета AccessTables, который затем будет использован Foreach loop container,

  7. В базе данных SQL Server создайте хранимую процедуру с именем dbo.CreateTable используя скрипт, предоставленный в разделе SQL Scripts. Эта хранимая процедура создаст таблицу в SQL Server, если она еще не существует. Make sure that you alter the table schema defined in the stored procedure according to your needs.

  8. Настройте Foreach loop container как показано на скриншотах № 8 и № 9.

  9. Настройте задачу "Выполнение SQL", как показано на снимках экрана № 10 и № 11.

  10. На этом этапе мы не можем настроить задачу потока данных, поскольку таблицы не существуют в SQL Server. Итак, мы выполним пакет на этом этапе, чтобы структуры таблицы Access были созданы в SQL Server. Снимок экрана № 12 показывает пример выполнения пакета. Снимок экрана № 13 показывает, что структуры таблиц были созданы в SQL Server, но они еще не заполнены данными.

  11. Теперь мы настроим Data Flow Task, Поместите OLE DB Source а также OLE DB Destination внутри задачи потока данных. Подключите источник OLE DB к назначению OLE DB. Смотрите скриншот №14.

  12. Настройте OLE DB Source как показано на скриншотах № 15 и № 16.

  13. Настройте OLE DB Destination как показано на скриншотах № 17 и № 18.

  14. Снимок экрана № 19 показывает пример выполнения пакета в Data Flow Task,

  15. Снимок экрана № 20 показывает, что таблицы SQL Server теперь заполнены данными из таблиц доступа.

Этот пример будет работать только для таблиц, имеющих одинаковую структуру, но различающихся по имени. Если другая таблица с именем Employees добавлены в доступ только с колонками 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 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:

1

Снимок экрана № 2:

2

Снимок экрана № 3:

3

Снимок экрана № 4:

4

Снимок экрана № 5:

5

Снимок экрана № 6:

6

Снимок экрана № 7:

7

Снимок экрана № 8:

8

Снимок экрана № 9:

9

Снимок экрана № 10:

10

Снимок экрана № 11:

11

Снимок экрана № 12:

12

Снимок экрана № 13:

13

Снимок экрана № 14:

14

Снимок экрана № 15:

15

Снимок экрана № 16:

16

Снимок экрана № 17:

17

Снимок экрана № 18:

18

Снимок экрана № 19:

19

Снимок экрана № 20:

20

Источник

Как кто-то, у кого есть доступ к Access, я сначала исправил бы данные в Access (т. Е. Объединил несколько таблиц в главные таблицы), а затем использовал помощник по миграции SQL Server для Access для увеличения размера. Это позволяет вам смоделировать импорт и исправить любые проблемы, прежде чем вы действительно это сделаете.

Первым этапом объединения таблиц данных я бы просто кодировал в VBA, хотя, вероятно, мне пришлось бы создать несколько таблиц с метаданными, которые отображали бы, что импортируется во что (если только таблицы не используют соглашения об именах, которые позволяют это определять алгоритмически).

Источник

Вы можете поместить результаты задачи sql в переменную типа object object. Затем эта переменная будет доступна для использования в задаче цикла.

Внутри цикла for вы можете изменить имя таблицы, с которой вы работаете, используя выражения.

После быстрого просмотра этой статьи может быть подробно описана первая часть процесса:

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

Источник
Другие вопросы по тегам