Ssis создание пакетов: Службы SSIS: создание пакета ETL — SQL Server Integration Services (SSIS)

Службы SSIS: создание пакета ETL — SQL Server Integration Services (SSIS)

Twitter




LinkedIn




Facebook




Адрес электронной почты

• 
  Статья
• 
  12/20/2022
• Чтение занимает 3 мин
• 
  

Область применения: SQL Server (все поддерживаемые версии) SSIS Integration Runtime в Фабрике данных Azure

Из этого руководства вы узнаете, как использовать конструктор Integration Services для создания пакета служб Microsoft SQL Server Службы Integration Services. Этот пакет получает данные из неструктурированного файла, преобразует их, а затем вставляет преобразованные данные в таблицу фактов. На следующих занятиях пакет будет расширен, чтобы продемонстрировать циклическую обработку, конфигурацию пакетов, ведение журнала и поток ошибок.

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

Что представляют собой службы SQL Server Integration Services (SSIS)?

службы Майкрософт SQL Server Integration Services (SSIS) — это платформа для создания высокопроизводительных решений интеграции данных, включая пакеты извлечения, преобразования и загрузки (ETL) для хранения данных. Службы SSIS содержат графические инструменты и мастера для построения и отладки пакетов; задачи для выполнения функций рабочего процесса, таких как операции FTP, выполнение инструкций SQL и отправка сообщений электронной почты; источники данных и назначения для извлечения и загрузки данных; преобразования для очистки, статистической обработки, слияния и копирования данных; базу данных управления, SSISDB для администрирования выполнения и хранения пакетов, а также API для программирования объектной модели Службы Integration Services.

Новые знания

Новые средства, элементы управления и возможности служб Microsoft SQL Server Службы Integration Services лучше всего изучать на практике. В этом учебнике с помощью конструктора SSIS вы создадите пакет ETL, который включает циклическую обработку, конфигурации, логику потока ошибок и ведение журнала.

Предварительные требования

Этот учебник предназначен для пользователей, знакомых с основными операциями с базами данных, но имеющих ограниченное представление о новых функциях служб SQL Server Службы Integration Services.

Для работы с этим руководством необходимо наличие следующих компонентов:

• SQL Server и Службы Integration Services. Чтобы установить SQL Server и служб SSIS, см. руководство по установке Integration Services.

• Пример базы данных AdventureWorksDW2012. Чтобы скачать базу данных AdventureWorksDW2012, скачайте файл AdventureWorksDW2012.bak со страницы с образцами баз данных AdventureWorks и выполните восстановление из резервной копии.

• Файлы с примерами данных. Образцы данных включаются в состав с пакетами занятий по службам Integration Services . Чтобы скачать образец данных и пакеты занятий в виде ZIP-файла, перейдите к файлам учебника в составе документации по SQL Server Integration Services.

  • Большая часть файлов в ZIP-файле доступна только для чтения во избежание непреднамеренных изменений. Для записи выходных данных в файл или его изменении может потребоваться отключить атрибут «только для чтения» в свойствах файла.
  • При использовании пакетов примеров предполагается, что файлы данных находятся в папке C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\Tutorial\Creating a Simple ETL Package. Если распаковать скачанные файлы в другое расположение, может потребоваться обновить путь к файлу в нескольких местах в пакетах с примерами.

Занятия этого учебника

Занятие 1. Создание проекта и основного пакета с помощью служб SSIS
На этом занятии вы создадите простой пакет ETL, который получает данные из неструктурированного файла, преобразует их с использованием преобразования «Уточняющий запрос» и загружает результат в целевую таблицу фактов.

Занятие 2. Добавление циклов с помощью служб SSIS
На этом занятии будет расширен пакет, созданный на занятии 1, чтобы использовать новые возможности циклической обработки для извлечения нескольких неструктурированных файлов в едином процессе потока данных.

Занятие 3. Добавление журналов с помощью служб SSIS
На этом занятии вы расширите пакет, созданный на занятии 2, чтобы использовать новые возможности ведения журнала.

Занятие 4. Добавление перенаправления потока ошибок с помощью служб SSIS
На этом занятии вы расширите пакет, созданный на занятии 3, чтобы использовать новые конфигурации вывода ошибок.

Занятие 5. Добавление конфигураций пакетов SSIS в модель развертывания пакетов
На этом занятии вы расширите пакет, созданный на занятии 4, чтобы использовать новые параметры конфигурации пакета.

Занятие 6. Использование параметров в модели развертывания проекта в службах SSIS
На этом занятии вы расширите пакет, созданный на занятии 5, чтобы воспользоваться преимуществами новых параметров в модели развертывания проекта.

Следующие шаги

Начните работу с учебником, открыв Занятие 1. Создание проекта и основного пакета с помощью служб SSIS.

Урок 1. Создание проекта и простого пакета с помощью служб SSIS — SQL Server Integration Services (SSIS)

Twitter




LinkedIn




Facebook




Адрес электронной почты

• 
  Статья
• 
  12/20/2022
• Чтение занимает 2 мин
• 
  

Область применения: SQL Server (все поддерживаемые версии) SSIS Integration Runtime в Фабрике данных Azure

На этом занятии вы создадите простой пакет для извлечения, преобразования и загрузки, который будет извлекать данные из отдельного источника неструктурированных файлов, преобразовывать их с помощью двух преобразований типа «уточняющий запрос», а затем записывать преобразованные данные в копию таблицы фактов FactCurrencyRate, находящуюся в образце базы данных AdventureWorksDW2012. На этом занятии вы узнаете, как создавать пакеты, добавлять и настраивать подключения к источникам данных и назначениям, а также работать с новыми компонентами потока управления и потока данных.

Перед созданием пакета необходимо ознакомиться с форматированием в источнике данных и назначении. После этого можно определить преобразования, необходимые для сопоставления источника данных с назначением.

Предварительные требования

Для выполнения упражнений в этом учебнике требуются средства Microsoft SQL Server Data Tools, набор примеров пакетов и образец базы данных.

• Инструкции по установке SQL Server Data Tools см. на странице Скачать SQL Server Data Tools.

• Чтобы скачать все пакеты занятий этого учебника, выполните указанные ниже действия.

  1. Перейдите к учебным файлам для служб Integration Services.

  2. Нажмите кнопку Скачать.

  3. Выберите файл Creating a Simple ETL Package.zip и нажмите кнопку Далее.

  4. Когда файл скачается, распакуйте его содержимое в локальный каталог.

• Чтобы установить и развернуть образец базы данных AdventureWorksDW2012, см. страницу Установка и настройка образца базы данных AdventureWorks — SQL.

Обзор исходных данных

Для этого учебника исходные данные представлены в виде набора курсов валют, содержащегося в неструктурированном файле SampleCurrencyData.txt. Данные источника в этом файле имеют четыре столбца: средний курс валюты, ключ валюты, ключ даты и курс на конец дня.

Вот пример исходных данных в файле SampleCurrencyData.txt:

1.00070049USD9/3/05 0:001.001201442  
1.00020004USD9/4/05 0:001  
1.00020004USD9/5/05 0:001.001201442  
1.00020004USD9/6/05 0:001  
1.00020004USD9/7/05 0:001.00070049  
1.00070049USD9/8/05 0:000.99980004  
1.00070049USD9/9/05 0:001.001502253  
1.00070049USD9/10/05 0:000.99990001  
1.00020004USD9/11/05 0:001.001101211  
1. 00020004USD9/12/05 0:000.99970009

При работе с исходными данными неструктурированных файлов важно понимать, как диспетчер подключений к неструктурированным файлам интерпретирует их. Если неструктурированный файл является документом в Юникоде, диспетчер соединений с неструктурированными файлами определяет все столбцы как [DT_WSTR] с шириной, по умолчанию равной 50. Если неструктурированный файл является документом в кодировке ANSI, столбцы определяются как [DT_STR] с шириной по умолчанию, равной 50. Возможно, потребуется изменить эти настройки, чтобы оптимизировать столбцы для конкретных данных. Необходимо узнать тип данных в назначении, а затем выбрать этот тип в диспетчере подключений к неструктурированным файлам.

Обзор данных назначения

Назначением исходных данных является копия таблицы фактов FactCurrencyRate в базе данных AdventureWorksDW. Таблица фактов FactCurrencyRate имеет четыре столбца и связи с двумя таблицами измерений, как показано в следующей таблице.

Сопоставление исходных данных с назначением

Анализ форматов данных источника и назначения показывает, что для значений CurrencyKey и DateKey требуются уточняющие запросы. Преобразования, которые выполняют эти уточняющие запросы, получают эти значения из таблиц измерений DimCurrency и DimDate.

Задачи занятия

Это занятие содержит следующие задачи.

• Шаг 1. Создание проекта служб Integration Services

• Шаг 2. Добавление и настройка диспетчера соединений с неструктурированными файлами

• Шаг 3. Добавление и настройка диспетчера подключений OLE DB

• Шаг 4. Добавление задачи потока данных к пакету

• Шаг 5. Добавление и настройка источника «Неструктурированный файл»

• Шаг 6. Добавление и настройка преобразований «Уточняющий запрос»

• Шаг 7. Добавление и настройка назначения OLE DB

• Шаг 8. Добавление заметок и форматирование пакета занятия 1

• Шаг 9. Тестирование пакета занятия 1

Начало занятия

Шаг 1. Создание проекта служб Integration Services

Что такое пакет SSIS и как его создать?

SQL Server Integration Services (SSIS) — это служба оркестрации рабочих процессов, используемая в основном для интеграции и преобразования данных. Впервые он был выпущен как часть SQL Server 2005, и сегодня он продолжает получать обновления и новые функции. Хотя службы SSIS можно установить и запустить на сервере (локально или в облаке), они также доступны в Фабрике данных Azure.

Службы SSIS обычно используются для выполнения таких задач, как:

• Перемещение данных из одной базы данных в другую
• Экспорт файлов из базы данных в файловую систему
• Загрузка файлов с FTP-сервера
• Извлечение данных из оперативной базы данных, их преобразование и загрузка в хранилище данных
• Очистка и стандартизация адресных данных
• Резервное копирование баз данных SQL Server
• Обработка моделей Analysis Services

Первоначально службы SSIS работали только в Windows, но с 2017 года они также доступны для Linux.

Создание пакета служб SSIS

Чтобы использовать службы SSIS, вы обычно создаете проект служб SSIS в Visual Studio (или инструментах данных SQL Server, в зависимости от вашей версии служб SSIS). В Visual Studio 2019 есть расширение SSIS, которое необходимо установить. Для более старых версий Visual Studio существует автономный установщик SQL Server Data Tools (SSDT) ​​со средой разработки SSIS.

Пакеты служб SSIS также можно создавать программно. Обычно это делается с помощью сценариев .NET или языка разметки Business Intelligence (BIML).

Расширение SSIS в Visual Studio предоставляет визуальную среду разработки, в которой можно создать проект SSIS. Вместо того, чтобы смотреть на код, вы увидите блоки, представляющие задачи, и линии, соединяющие их.

Проект SSIS содержит:

• Один или несколько пакетов
• Ноль или более параметров уровня проекта
• Ноль или более диспетчеров соединений на уровне проекта

Пакет — это единица выполнения в службах SSIS. Хотя технология не помешает вам объединить все ваши задачи в один большой пакет, лучше всего сделать каждый пакет для одной цели. Целью может быть загрузка данных в определенную таблицу измерений или загрузка определенного файла с FTP-сервера. Некоторые пакеты действуют как пакеты оркестрации, не выполняя сами фактическую работу, а вызывая другие пакеты в определенном порядке при определенных условиях.

Части пакета служб SSIS

Основными частями структуры пакета служб SSIS являются поток управления, поток данных и диспетчеры соединений. Это показано на рис. 1 .

Рис. 1. Основные компоненты пакета SSIS включают диспетчеры соединений, поток управления и поток данных

Другие объекты пакета расширяют его функциональные возможности. К ним относятся:

• Параметры
• Переменные
• Обработчики событий
• Конфигурации
• Ведение журналов и поставщики журналов

Поток управления

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

Общие задачи потока управления включают задачу «Выполнение пакета», задачу «Выполнение процесса», задачу «Файловая система», задачу «Сценарий», задачу «Выполнение SQL» и задачу «Поток данных». Использование этих задач вместе в пакете позволяет, например, копировать данные с файлового сервера в базу данных, перемещать файл в архивную папку и отправлять электронное письмо в случае сбоя выполнения.

Сторонние задачи могут быть добавлены к задачам, включенным в SSIS. Они часто предоставляют более простой способ выполнения действий, которые обычно требуют написания кода C# в задаче-скрипте или использования непонятных расширенных конфигураций для задачи. Это может включать чтение свойств файла, выполнение сценария PowerShell или распаковку файла.

Поток данных

Задача потока данных — это специальный тип задачи, предназначенный для выполнения в потоке управления. Он использует конвейер в памяти для перемещения и преобразования данных. Поскольку это требует дополнительной настройки, в Visual Studio есть отдельная вкладка для проектирования потоков данных.

Существует три типа компонентов потока данных: источники, преобразования и адресаты. Исходный компонент извлекает данные из источника, такого как представление базы данных или файл на сервере. Компонент преобразования можно использовать для изменения, суммирования или очистки данных. Компоненты назначения загружают данные в хранилище данных или создают набор данных в памяти. Общие компоненты преобразования включают преобразование «Уточняющий запрос», преобразование «Условное разбиение», преобразование «Преобразование данных», преобразование «Подсчет строк» ​​и преобразование «Объединить все».

В дополнение к компонентам потока данных, включенным в состав служб SSIS, существуют сторонние компоненты потока данных, предназначенные для использования при разработке. Они могут быть источниками или пунктами назначения, обеспечивающими более простой или более эффективный доступ к хранилищу данных. Или это могут быть преобразования, выполняющие обычно полезную задачу, такую ​​как хеширование значений, преобразование часового пояса или замена нежелательных символов. Часто для этого можно создать собственный метод, но использование стороннего компонента может сэкономить время разработки или повысить производительность потока данных.

На рис. 2 показан простой поток управления и поток данных для пакета SSIS. Этот пакет может выполняться как часть процесса загрузки хранилища данных. Его единственная цель — получить данные учетной записи из Microsoft Dynamics 365 и поместить их в промежуточную таблицу в базе данных.

Рис. 2. Поток управления для пакета SSIS усекает промежуточную таблицу, извлекает данные из Dynamics 365 и вставляет их в промежуточную таблицу

Поток управления состоит из четырех шагов:

1. Выполнение хранимой процедуры для регистрации начала выполнения пакета
2. Усекать промежуточную таблицу в базе данных SQL Server
3. Выполнение задачи потока данных
4. Выполнение хранимой процедуры для регистрации завершения выполнения пакета

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

Задача потока данных, которая выполняется как третий шаг в потоке управления, состоит из четырех шагов:

1. Получение данных из объекта учетной записи в Microsoft Dynamics 365 (исходный компонент)
2. Подсчитать количество извлеченных строк и сохранить результат в переменной (компонент преобразования)
3. Добавить производные столбцы с помощью языка выражений служб SSIS (компонент преобразования)
4. Вставьте полученные данные в таблицу в базе данных SQL Server (целевой компонент)

Исходный компонент в потоке данных является сторонним компонентом из SolarWinds Task Factory, а последующие компоненты являются собственными компонентами SSIS.

Каждый поток данных может содержать несколько источников, адресатов и преобразований. Вы можете подключить выход одного компонента к входу следующего компонента, чтобы создать различные пути. Это позволяет создавать потоки данных, предназначенные для извлечения нескольких файлов из разных мест, объединения данных из всех файлов, удаления повторяющихся строк и вставки данных в таблицу базы данных. Поток данных также может записывать данные в отдельное место назначения, если выполнение потока данных завершается сбоем.

Выполнение пакета SSIS

Пакеты SSIS можно запускать вручную в Visual Studio в целях тестирования. Обычно они развертываются в каталоге служб SSIS (с использованием модели развертывания проекта) или в файловой системе (с использованием модели развертывания пакетов) для автоматического выполнения.

Агент SQL Server обычно используется для планирования выполнения пакетов служб SSIS. Пакеты также можно выполнять с помощью утилиты командной строки, сценария T-SQL или стороннего планировщика. Это позволяет инициировать выполнение пакета SSIS по расписанию (например, каждый день в 2 часа ночи) или по событию (например, файл загружается на сервер).

Ускорьте время разработки

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

Обязательно посмотрите Фабрику задач SolarWinds. Это может сэкономить ваше время при создании высокопроизводительных пакетов SSIS и устранить утомительные задачи программирования для групп разработчиков, использующих SSIS.

Меган Лонгория — участник ActualTech Media Contributor, эксперт по базам данных и консультант по бизнес-аналитике в Denny Cherry & Associates, блоггер, спикер, автор, технический редактор и MVP Microsoft Data Platform.

Шаг 1. Создайте новый проект служб Integration Services — Службы интеграции SQL Server (SSIS)

Редактировать

Твиттер

LinkedIn

Фейсбук

Электронное письмо

• Статья
• 18. 11.2022
• 2 минуты на чтение

Применимо к:
SQL Server (все поддерживаемые версии)
Среда выполнения интеграции SSIS в фабрике данных Azure

Первым шагом в создании пакета в службах Integration Services является создание проекта служб Integration Services. Этот пример проекта включает шаблоны для источников данных, представлений источников данных и пакетов, составляющих решение для преобразования данных.

Пакеты, которые вы создаете в этом руководстве по службам Integration Services, интерпретируют значения данных, зависящих от языкового стандарта. Если ваш компьютер не настроен на использование региональной опции Английский (США) , вам необходимо установить дополнительные свойства в пакете.

Пакеты, которые вы используете на уроках со 2 по 6, скопированы из пакета, созданного вами на этом уроке.

Примечание

Если вы еще этого не сделали, ознакомьтесь с предварительными требованиями урока 1.

Создать новый проект служб Integration Services

1. В меню Пуск Windows найдите и выберите Visual Studio (SSDT) ​​ .

2. В Visual Studio выберите Файл > Новый > Проект , чтобы создать новый проект служб Integration Services.

3. В диалоговом окне New Project разверните узел Business Intelligence в Installed и выберите Integration Services Project в Панель шаблонов .

4. В поле Name измените имя по умолчанию на SSIS Tutorial . Чтобы использовать уже существующую папку, снимите флажок Создать каталог для решения .

5. Примите расположение по умолчанию или выберите Browse , чтобы найти нужную папку. В диалоговом окне Расположение проекта выберите папку, а затем Выберите папку .