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

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

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

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

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

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


ssis ms-access foreach-loop-container
person Loki70    schedule 09.06.2011    source источник

Ответы (3)


arrow_upward
19
arrow_downward

Вот один из возможных способов загрузки данных 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 теперь заполняются данными из таблиц Access.

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

person Community    schedule 10.06.2011
comment
Шива, я должен сказать: это был самый исчерпывающий и хорошо написанный ответ, который я КОГДА-ЛИБО получал. Используя ваши примеры и скриншоты, я смог создать свой пакет. Мне пришлось внести небольшое изменение здесь или там, чтобы справиться с моей конкретной базой данных, но ваша запись сделала это очень понятным. БОЛЬШОЕ СПАСИБО за потраченное время на написание всего этого! Ты действительно выручил меня из варенья. - person Loki70; 12.06.2011
comment
Это отличная информация! Кто-нибудь адаптировал это для работы в цикле ForEach, который должен загружать все базы данных доступа в каталоге? Я сделал простой тест, и похоже, что он не будет работать, если я не смогу закрыть соединение OleDB перед изменением ConnectionString, указывающей следующую базу данных Access для загрузки. Есть предположения? - person ; 05.08.2011
comment
@Siva, не могли бы вы уточнить, что вы подразумеваете под пока все таблицы в Access имеют одинаковую структуру? - person Agent007; 08.02.2013

arrow_upward
0
arrow_downward

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

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

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

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

person Sam    schedule 09.06.2011

arrow_upward
0
arrow_downward

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

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

person David-W-Fenton    schedule 12.06.2011