Как создать временную таблицу в задаче потока управления 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.

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

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

Выберите 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 в диспетчере подключений

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

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

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

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

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

Перетащите 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

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

Дважды щелкните значок 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 страница

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

Чтобы исправить ошибку, выполните инструкцию 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 страница

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

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

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

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

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

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

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

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

Ответ 2

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

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