Новые возможности SQL Server 2005 Integration Services

       

Учебное пособие: Создание первого пакета SSIS


Теперь, имея хорошую теоретическую подготовку, Вы готовы к созданию своего первого пакета. В данном учебном пособии мы будем производить преобразование данных из плоского файла, содержащего около 9000 записей, агрегацию данных, их группировку, упорядочивание перед записью в новый текстовый файл. В SQL Server 2000 DTS все это требовало сложного кодирования или добавления данных в промежуточную таблицу, с которой и производились действия. В SQL Server 2005 Вам не нужны ни промежуточная таблица, ни какие то знания по программированию. Все это сильно уменьшит время создания Вашей ETL системы.

Для начала откройте Business Intelligence Development Studio. В меню File выберите пункт New, а затем Project.

Как предложено в данной демонстрации дайте пакету имя FirstPackage, как это видно на следующем рисунке.

Рисунок 71

После нажатия на OK основной проект будет создан и Вы увидите экран, как это показано на Рисунке 72.

Рисунок 72

В данном месте файл Package.dtsx должен быть открытым как часть процесса создания проекта. Если этого не случилось, то в Solution Explorer дважды нажмите кнопку мыши на этом пакете.

Начнем мы с создания соединений, которые будем использовать позднее.

Для добавления первого соединения для плоского файла нажмем правую кнопку мыши в закладке Connections (она расположена внизу экрана) и выберем New Flat File Connection. Откроется диалог Flat File Connection Manager Editor (см. Рисунок 73).

В закладке General этого диалога Вы можете задать большинство установок для плоского файла. Для свойства Connection Manager Name выберите значение Input File. Имя файла должно содержать и местоположение, и имя файла SampleFile.txt.

Далее убедитесь, что Вы указали двойные кавычки (") как признак текстовых столбцов и выбрали параметр Column names in first data row. Признак текстового столбца укажет механизму SSIS, что данные заключены внутри двойных кавычек. Такая инкапсуляция позволяет использовать в качестве разделителя такие символы как, например, запятая.



ф


Рисунок 73

Перейдем к закладке Columns в левой панели того же диалога и удостоверимся, что мы видим столбцы и несколько первых записей наших данных. Далее перейдем в закладку Advanced(см. Рисунок 74). Здесь, перебирая столбцы, Вы можете увидеть, какие типы данных SSIS будет использовать для каждого из них. Выберите столбец по имени TransactionDate и для типа данных выберите "date [DT_DATE]". Для столбца Quantity также определите тип данных four-byte signed integer [DT_I4]".

Очень полезной особенностью здесь является наличие кнопка Suggest Type, которая заставляет DTS извлечь тестовые данные и предсказать какой тип они имеют. При автоматическом определении типов данных это будет сделано для всех столбцов, а не только для текущего выбранного. Всегда проверяйте сделанные выборы перед нажатием OK.



Рисунок 74



После нажатия OK Вы увидите соединение, расположенное в закладке Connections. Создайте еще одно соединение для файла SummarybyProductID.txt. В этом соединении выберите все установки по-умолчанию, кроме признака текстовых столбцов, которому нужно задать значение double-quotes. Назовите это соединение Output File.

Слева Вы должны видеть закладку Toolbox. Перетащите с нее элемент задачи DataFlow на закладку Control Flow.

Внимание: Вы можете зафиксировать положение Toolbox в своей среде разработки путем нажатия соответствующей иконки.

Нажните на вновь созданной задаче правую кнопку мыши и выберите Rename. Впишите "Transform Flat File" в качестве нового имени задачи. (Вы также можете нажать кнопку мыши на задаче и изменить свойство Name в панели Properties).

Нажмите правую кнопку мыши на переименованной задаче Data Flow и выберите Edit. Откроется закладка Data Flow.

В закладке Data Flow перетащите из Toolbox в область редактирования элемент потока данных по имени Flat File Source. Вы можете переименовать полученный элемент потока данных, нажимая на нем правую кнопку мыши и выбирая Rename. Переименуйте его в "Sample File".

Нажмите правую кнопку мыши на потоке данных Sample File и выберите Edit. Убедитесь что для параметра Connection из левой панели в Sample File задано в качестве соединения. Затем перейдите в раздел Columns и уберите отметку у большинства столбцов, оставив только ProductID, TransactionDate, Quantity, и ActualCost. Эти столбцы и будут нашим доступным потоком для всего SSIS пакета (см. Рисунок 75).



Рисунок 75

Теперь нам нужно создать новый столбец, который будет доступен в потоке. Он будет содержать количество единиц товара (столбец Quantity) умноженное на его стоимость (столбец ActualCost). Для этого перетащите задачу Derived Column на область редактирование чуть ниже соединения Sample File. Нажмите правую кнопку мыши на этой задаче и выберите Rename. Задайте новое имя "Total Cost". Выберите соединение Sample File и Вы увидите зеленую и красную стрелки направленные вниз. Зеленая стрелка определяет, что произойдет в случае, если этот шаг завершиться успешно. Красная стрелка определяет, что произойдет в случае, если этот шаг завершиться неуспешно. Выберите зеленую стрелку и перетащите ее на задачу Total Cost.

После соединения этих двух элементов нажмите правую кнопку мыши на элементе Total Cost и выберите Edit. Откроется диалог Derived Column Editor.

Раскройте расположенное справа дерево Columns и перетащите столбцы Quantity и ActualCost в столбец Expression, как показано на Рисунке 76. Укажите в столбце Expression выражение Quantity*ActualCost. Если Вы получите сообщение об ошибке типов данных, то Вам следует перечитать первую часть данного пособия, в которой говорилось о задании типов данных каждому столбцу данного соединения.

Измените имя вычисляемого столбца на TotalCost, а его тип на Currency, как показано на Рисунке 76. После этого нажмите OK. Все это приведет к созданию дополнительного столбца, который можно увидеть в преобразованиях в следующих задач и который будет содержать произведение двух столбцов.

Рисунок 76

После создания нового вычисляемого столбца мы готовы перейти к агрегированию данных по продуктам. Перетащите задачу Aggregate из Toolbox на панель редактирования. Соедините задачу Total Cost и новую задачу с помощью зеленой стрелки. И переименуйте задачу в "Group by Product."

Откройте диалог Aggregate Editor путем нажатия правой кнопки мыши на задаче Group by Product с последующим выбором Edit. В Aggregate Editor задайте желаемый способ агрегации для соединения. Важно отметить, что до сих пор мы не занесли еще ни одной записи, ни в одном из источников. Все происходит только в памяти.

В диалоге Aggregate Editor (см. Рисунок 77) выберите столбцы ProductID, TransactionDate, Quantity, и TotalCost. Это частично заполнит область по имени Input Column.

Затем задайте тип операции, которая будет выполнена для каждого из столбцов. Мы желает осуществить группировку по ProductID, поэтому выберем для него тип Group By. Для столбцов Quantity и TotalCost выберем операцию Sum. И, наконец, для столбца TransactionDate - Maximum. Также измените значение свойства Output Aliases для двух новых столбцов, чтобы дать им уникальные имена. Таким образом, мы получили группировку по столбцу ProductID с максимальными значениями по столбцу TransactionDate и суммами по столбцам количества и цены.

Для сохранения изменений нажмите OK. Вы можете заметить, что столбец TotalCost теперь стал доступен в данной задаче. Как и было сказано, добавленные вычисляемые столбцы становятся видимыми в последующих задачах.



Рисунок 77

Теперь, когда данные агрегируются по ProductID, мы готовы к сортировке данных для их более понятного восприятия в отчете. Перетащите задачу Sort (см. Рисунок 78) из Toolbox и переименуйте ее в "Order by ProductID". Убедитесь, что эта задача соединена с задачей Group by Product зеленой стрелкой. Откройте задачу для изменения, как и все предыдущие. Выберите столбец ProductID и установите для него упорядочивание по возрастанию. Нажмите OK для завершения.

Рисунок 78

В завершении нам нужно вывести результаты работы в другой плоский файл. Перетащите задачу Flat File Destination из Toolbox. Соедините ее с задачей Order by ProductID путем перетягивания между ними зеленой стрелки.

Выберите Edit для изменения свойств приемника Flat File

Выберите New для создания нового соединения с плоским файлом.

Переименуйте соединение в Output File (подробности см. на Рисунке 79).

Убедитесь, что в разделе Preview видны все выходные столбцы предыдущих элементов.

В разделе Mappings убедитесь, что преобразование выглядит правильно. Нажмите OK.

Вы закончили создание пакета. Для его запуска нажмите F5 или в меню Start выберите пункт Debug. Вы увидите, как начнет увеличиваться счетчик записей. На Рисунке 79 показан конечный результат всех действий.

Рисунок 79

Выбрав закладку Progress (см.Рисунок 80) Вы сможете увидеть подробное описание процесса выполнения SSIS.

Рисунок 80


Содержание раздела