Как создать временную таблицу в задаче потока управления SSIS, а затем использовать ее в задаче потока данных?

У меня есть поток управления, где я создаю базу данных temp и таблицу в команде с T-SQL. Когда я добавляю поток данных, я хотел бы запросить таблицу, но я не могу, потому что таблицы не существует для захвата информации. Когда я пытаюсь, я получаю ошибки о входе в систему, потому что база данных еще не существует (пока). У меня есть проверка достоверности.

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

Если есть лучший способ создать временную промежуточную базу данных и запросить ее в потоках данных, пожалуйста, дайте мне знать.

Ответы

Ответ 1

Решение:

Задайте свойство RetainSameConnection на Connection Manager до True, чтобы временная таблица, созданная в одной задаче потока управления, могла быть сохранена в другой задаче.

Вот пример пакета SSIS, написанного в SSIS 2008 R2, который иллюстрирует использование временных таблиц.

Краткое руководство:

Создайте хранимую процедуру, которая создаст временную таблицу с именем ##tmpStateProvince и заполнит несколько записей. Образец пакета SSIS сначала вызовет хранимую процедуру, а затем отобразит данные временной таблицы, чтобы заполнить записи в другой таблице базы данных. Образец пакета будет использовать базу данных с именем Sora Использовать ниже создайте хранимую процедуру script.

USE Sora;
GO

CREATE PROCEDURE dbo.PopulateTempTable
AS
BEGIN

    SET NOCOUNT ON;

    IF OBJECT_ID('TempDB..##tmpStateProvince') IS NOT NULL
        DROP TABLE ##tmpStateProvince;

    CREATE TABLE ##tmpStateProvince
    (
            CountryCode     nvarchar(3)         NOT NULL
        ,   StateCode       nvarchar(3)         NOT NULL
        ,   Name            nvarchar(30)        NOT NULL
    );

    INSERT INTO ##tmpStateProvince 
        (CountryCode, StateCode, Name)
    VALUES
        ('CA', 'AB', 'Alberta'),
        ('US', 'CA', 'California'),
        ('DE', 'HH', 'Hamburg'),
        ('FR', '86', 'Vienne'),
        ('AU', 'SA', 'South Australia'),
        ('VI', 'VI', 'Virgin Islands');
END
GO

Создайте таблицу с именем dbo.StateProvince, которая будет использоваться в качестве таблицы назначения для заполнения записей из временной таблицы. Используйте таблицу create script для создания таблицы назначения.

USE Sora;
GO

CREATE TABLE dbo.StateProvince
(
        StateProvinceID int IDENTITY(1,1)   NOT NULL
    ,   CountryCode     nvarchar(3)         NOT NULL
    ,   StateCode       nvarchar(3)         NOT NULL
    ,   Name            nvarchar(30)        NOT NULL
    CONSTRAINT [PK_StateProvinceID] PRIMARY KEY CLUSTERED
        ([StateProvinceID] ASC)
) ON [PRIMARY];
GO

Создайте пакет SSIS с помощью Business Intelligence Development Studio (BIDS). Щелкните правой кнопкой мыши на вкладке "Менеджеры подключения" внизу пакета и нажмите New OLE DB Connection..., чтобы создать новое соединение для доступа к базе данных SQL Server 2008 R2.

Connection Managers - New OLE DB Connection

Нажмите New... в разделе Настройка диспетчера подключений OLE DB.

Configure OLE DB Connection Manager - New

Выполните следующие действия в диалоговом окне диспетчера подключений.

  • Выберите Native OLE DB\SQL Server Native Client 10.0 поставщика, так как пакет подключится к базе данных SQL Server 2008 R2
  • Введите имя сервера, например MACHINENAME\INSTANCE
  • Выберите Use Windows Authentication из раздела "Вход в сервер" или в зависимости от того, что вы предпочитаете.
  • Выберите базу данных из Select or enter a database name, образец использует имя базы данных Sora.
  • Нажмите Test Connection
  • Нажмите OK на тестовом соединении с успешным сообщением.
  • Нажмите OK в диспетчере подключений

Connection Manager

Новое настроенное соединение с данными появится в разделе "Настройка диспетчера подключений OLE DB". Нажмите OK.

Configure OLE DB Connection Manager - Created

Диспетчер соединений OLE DB KIWI\SQLSERVER2008R2.Sora появится на вкладке "Диспетчер подключений" в нижней части пакета. Щелкните правой кнопкой мыши диспетчер подключений и выберите Properties

Connection Manager Properties

Задайте свойство RetainSameConnection в соединении KIWI\SQLSERVER2008R2.Sora значению True.

RetainSameConnection Property on Connection Manager

Щелкните правой кнопкой мыши в любом месте пакета, а затем нажмите Variables, чтобы просмотреть панель переменных. Создайте следующие переменные.

  • Новая переменная с именем PopulateTempTable типа данных String в области пакета SO_5631010 и установите переменную со значением EXEC dbo.PopulateTempTable.

  • Новая переменная с именем FetchTempData типа данных String в области пакета SO_5631010 и установите переменную со значением SELECT CountryCode, StateCode, Name FROM ##tmpStateProvince

Variables

Перетащите Execute SQL Task на вкладку "Поток управления". Дважды щелкните задачу Execute SQL Task, чтобы просмотреть редактор задач Execute SQL.

На странице General редактора задач Execute SQL выполните следующие действия.

  • Задайте имя Create and populate temp table
  • Задайте тип подключения OLE DB
  • Установите соединение KIWI\SQLSERVER2008R2.Sora
  • Выберите Variable из SQLSourceType
  • Выберите User::PopulateTempTable из SourceVariable
  • Нажмите OK

Execute SQL Task Editor

Перетащите a Data Flow Task на вкладку "Поток управления". Переименуйте задачу потока данных как Transfer temp data to database table. Подключите зеленую стрелку из Execute SQL Task к задаче потока данных.

Control Flow Tab

Дважды щелкните значок Data Flow Task, чтобы перейти на вкладку "Поток данных" . Перетащите OLE DB Source на вкладку "Поток данных" . Дважды щелкните источник OLE DB для просмотра исходного редактора OLE DB.

На странице Connection Manager исходного редактора OLE DB выполните следующие действия.

  • Выберите KIWI\SQLSERVER2008R2.Sora из диспетчера подключений OLE DB
  • Выберите SQL command from variable из режима доступа к данным
  • Выберите User::FetchTempData из имени переменной
  • Нажмите Columns страница

OLE DB Source Editor - Connection Manager

Нажав на Columns страницу в OLE DB Source Editor, вы увидите следующую ошибку, потому что таблица ##tmpStateProvince, указанная в исходной командной переменной, не существует, и SSIS не может прочитать определение столбца.

Error message

Чтобы исправить ошибку, выполните инструкцию EXEC dbo.PopulateTempTable с помощью SQL Server Management Studio (SSMS) в базе данных Sora, чтобы хранимая процедура создала временную таблицу. После выполнения хранимой процедуры нажмите кнопку Columns в редакторе исходного кода OLE DB, вы увидите информацию о столбце. Нажмите OK.

<Т411 >

Перетащите OLE DB Destination на вкладку "Поток данных" . Подключите зеленую стрелку от источника OLE DB к назначению OLE DB. Дважды щелкните OLE DB Destination, чтобы открыть OLE DB Destination Editor.

На странице Connection Manager редактора назначения OLE DB выполните следующие действия.

  • Выберите KIWI\SQLSERVER2008R2.Sora из диспетчера подключений OLE DB
  • Выберите Table or view - fast load из режима доступа к данным
  • Выберите [dbo].[StateProvince] из имени таблицы или вида
  • Нажмите Mappings страница

OLE DB Destination Editor - Connection Manager

Нажмите кнопку Mappings в редакторе назначения OLE DB, чтобы автоматически сопоставить столбцы, если имена столбцов ввода и вывода одинаковы. Нажмите OK. Столбец StateProvinceID не имеет соответствующего столбца ввода, и он определяется как столбец IDENTITY в базе данных. Следовательно, отображение не требуется.

OLE DB Destination Editor - Mappings

Вкладка "Поток данных" должна выглядеть примерно так после настройки всех компонентов.

Data Flow tab

Перейдите на вкладку OLE DB Source на вкладке "Поток данных" и нажмите F4, чтобы просмотреть Properties. Установите для свойства ValidateExternalMetadata значение False, чтобы SSIS не пыталась проверить наличие временной таблицы во время фазы проверки выполнения пакета.

Set ValidateExternalMetadata

Выполните запрос select * from dbo.StateProvince в SQL Server Management Studio (SSMS), чтобы найти количество строк в таблице. Перед выполнением пакета он должен быть пустым.

Rows in table before package execution

Выполните пакет. Поток управления показывает успешное выполнение.

Package Execution - Control Flow tab

В вкладке "Поток данных" вы заметите, что пакет успешно обработал строки 6. Хранимая процедура, созданная на ранних этапах вложенных вставленных 6 строк во временную таблицу.

Package Execution - Data Flow tab

Выполните запрос select * from dbo.StateProvince в SQL Server Management Studio (SSMS), чтобы найти строки 6, успешно вставленные в таблицу. Данные должны совпадать с найденными строками в хранимой процедуре.

Rows in table after package execution

В приведенном выше примере показано, как создать и использовать временную таблицу в пакете.

Ответ 2

Я опаздываю на эту вечеринку, но я хотел бы добавить один бит к user756519 тщательный, отличный ответ. Я не считаю, что свойство "RetainSameConnection on Connection Manager" имеет значение в этом случае на основе моего недавнего опыта. В моем случае важным моментом был их совет по установке "ValidateExternalMetadata" в False.

Я использую временную таблицу для облегчения копирования данных из одной базы данных (и сервера) в другую, поэтому причина "RetainSameConnection" не была актуальной в моем конкретном случае. И я не считаю, что важно выполнить то, что происходит в этом примере, насколько это возможно.