These SSIS Interview Questions Should Prepare You Well
SSIS is a crucial component of Microsoft SQL Server and an essential element of the Business Intelligence suite. The principal goal of SSIS is to resolve issues encountered in workflow applications and data integration.
Many users consider SSIS one of the top ETL tools in the market and the best buddy for skilled SQL developers. SSIS is suitable for developers dealing with huge and complex data volumes. The developers commonly use Visual Studio for writing complex codes, ensuring a big margin for errors.
If you are about to appear for an interview that is SSIS related, this article can serve as excellent preparation.
To start getting familiarity with SSIS integration with SQL, We recommend you to start with this course:
People are also reading:
Installing SQL Server Data Tools
First things first, make sure you have Visual Studio 2017 installed. You can install the latest version (15.6.4) here: https://docs.microsoft.com/en-us/visualstudio/install/install-visual-studio
Once the installation has completed, installing SSDT will add the full set of SQL Server tools into your existing Visual Studio installation. Install SSDT using this link: https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt
Once downloaded, run the file:
Click Next, then Install:
Once the installation completes, click the Windows symbol and open Visual Studio 2017 (SSDT):
This will prompt you to sign in to your Microsoft account:
Now that you have signed in and started SSDT, it is time to create your first project. For this you will use SQL Server Integration Services (SSIS).
Импорт простых справочников
Давайте напишем самый простой пример импорта. Выберем таблицу AddressType и скопируем данные в другую БД. Добавим только одно небольшое усложнение — будем копировать не все столбцы и один переименуем.
При написании импорта важно понимать, что все данные в одной цепочке как бы перетекают от начала в конец (возможно, «обрастая» по пути новыми столбцами и данными). Сначала создадим таблицу в тестовой БД
Это можно сделать в SQL Management Studio, можно и в SSIS, но это будет нелогично, потому что обычно таблицы в приёмнике созданы до импорта да и пример усложнится. Создаём таблицу с первичным ключом:
Сначала создадим таблицу в тестовой БД. Это можно сделать в SQL Management Studio, можно и в SSIS, но это будет нелогично, потому что обычно таблицы в приёмнике созданы до импорта да и пример усложнится. Создаём таблицу с первичным ключом:
create table AddressType(Id int not null, Name nvarchar(50) not null,constraint PK_AddressType primary key(Id))
Теперь добавим в наш пакет действие по импорту. Сначала добавляем и сразу переименовываем компонент Data Flow, который будет содержать этот блок импорта:
Дважды щёлкнув по компоненту, открываем вкладку «Data Flow»
Затем добавляем компонент ADO.NET Data Source, обратите внимание, что SSIS предлагает две стрелки с вариантами дальнейшего выполнения процесса (для обычных данных и в случае ошибки) и показывает иконкой ошибки (в данном случае не задан ConnectionManager):
Теперь добавим соединение и напишем запрос, получающий данные. Для этого дважды щёлкнем на компонент и зададим нужные значения (чтобы проверить себя можно нажать Preview):
В данном случае я ограничил и переименовал столбцы с помощью запроса. Можно было выбрать режим Table or view вместо SQL Command, но лично я обычно выбираю такой вариант, только если нужны все столбцы. Что касается переименования столбца, можно было этого не делать и просто позже по-другому связать столбцы (позже вы увидите, почему я предпочитаю делать так).
Теперь осталось добавить таблицу-приёмник. Если бы типы данных не совпадали, пришлось бы сделать промежуточный шаг с компонентом Data Conversion (он интуитивно понятный, поэтому сейчас его не рассматриваем). Перетаскиваем компонент ADO.NET Destination и цепляем к нему стрелку от источника:
Затем тоже щёлкаем дважды по компоненту и выбираем недавно созданную таблицу в тестовой базе:
Затем выбираем пункт Mappings, и студия делает его автоматически, потому что названия столбцов совпадают:
Осталось сохранить параметры и запустить наш импорт (F5):
Как видите, всё прошло успешно, и мы импортировали 6 строк в таблицу AddressType. Однако если вы закроете отладку и нажмёте F5 снова, вы увидите менее радостную картину:
О том что случилось и как этого избежать — далее.
The SSIS Toolset
SSIS is licensed as part of SQL Server, and the internals of running an SSIS package can be installed as part of the normal SQL Server install. As an SQL Server Integration Services (SSIS) developer, however, you will need a couple of additional tools installed on your workstation:
- SSDT: SSDT is a lighter version of Microsoft Visual Studio, configured with the extensions to create SSIS projects. When building or testing SSIS packages, SSDT is the tool in which you will spend most of your time.
- SQL Server Management Studio (SSMS): Although not strictly required for building SQL Server Integration Services packages, at some point during the development cycle you’ll need SSMS to test and deploy the ETL processes you create in SQL Server Integration Services.
When setting up your SSIS development environment, it’s important to remember that you must install the SSDT version of Visual Studio to be able to work with SQL Server Integration Services (SSIS) projects. If you already have Visual Studio installed, you’ll need to download and install the SSDT components.
Excel 2003-2016 and CSV Providers
Jet OLE DB Provider for Microsoft Excel 2003
The Microsoft.Jet.OLEDB.4.0 Provider is used to import-export data with Microsoft Excel 97-2003 workbooks.
It is named «Microsoft Excel» in the Import-Export Wizard.
This provider is available by default on all Windows platform but has only the 32-bit version.
So, use the 32-bit version of dtexec utility and specify Run64BitRuntime=False in the Debug Options of SQL Server Business Intelligence Development Studio project properties.
ACE OLE DB Provider for Microsoft Excel 2007-2016
Microsoft Office 12.0 Access Database Engine OLE DB Provider is used to import-export data with Microsoft Excel 2007-2016 workbooks like *.xlsx, *.xlsm, *.xlb.
You can download and install this provider using the following link:
Note that the platform of the provider should be the same as installed Microsoft Office platform.
Also, you should use the appropriate platform versions of dtexec and the Import-Export Wizard.
So, if you have Microsoft Office x64 installed, you should install the 64-bit provider and use the 64-bit Import-Export Wizard.
Note that SQL Server Management Studio and SQL Server Business Intelligence Development Studio run only the 32-bit Import-Export Wizard from a shell. So, use the «Import and Export Data (64-bit)» link from the SQL Server Start Menu group to run the wizard.
To configure the provider, fill in the Data Source value with an Excel workbook path and Extended Properties with the «Excel 12.0;HDR=YES» value for import to SQL Server from Excel, and the «Excel 12.0» value for export from SQL Server to Excel.
In the 32-bit Import-Export Wizard, you can use the «Microsoft Excel» data source with the «Microsoft Excel 2007» version to configure the ACE OLE DB Provider the same way as for Excel 2003 data source.
Преобразование данных.
В среде Integration Services реализовано достаточно много стандартных преобразователей.
Их рассмотрение выходит за рамки данной статьи, поэтому я приведу лишь результирующий поток данных:
Данные о клиентах и продажах загружаются из двух источников OLE DB Source — Customers List и Sales Report.
Далее они сортируются по полю CUST_NO при помощи двух компонентов Sort.
Предварительная сортировка требуется для компонента Merge (Merge customer countries and sales info).
В компоненте Merge данные из двух источников объединяются по полю CUST_NO.
В результрующее множество добавляются поля TOTAL_VALUE, DISCOUNT, ITEM_TYPE из отчета о продажах, а так же COUNTRY из списка клиентов:
Далее данные передаются компоненту Aggregate (Group by countries and items types),
который производит различные групповые операции над результирующим множеством:
Из рисунка следует, что сначала производится группировка по странам, далее по типу товара,
а так же определяется величина прибыли и число продаж по каждой стране. После этого компонент Sort сортирует данные
по величине прибыли и передает их виртуальному приемнику DataReaderDest. Результат обработки данных контролируется с
помощью средства Data Viewers:
Просмотром данных (DataViewers) удобно пользоваться для вывода промежуточных результатов и отладки SSIS пакета.
После выполнения пакета (F5), на экране появится окно с результатами обработки данных:
SSIS Tasks Types
In SSIS tool, you can add a task to control flow. There are different types of tasks which perform various kinds of works.
Some important SSIS tasks are mentioned below:
| Task Name | Descriptions |
|---|---|
| Execute SQL Task | As its name suggests, it will execute a SQL statement against a relational database. |
| Data Flow Task | This task can read data from one or more sources. Transform the data when it is in the memory and write it out against one or more destinations. |
| Analysis Services Processing Task | Use this task to process objects of a Tabular model or as an SSAS cube. |
| Execute Package Task | Use can use this SSIS task to execute other packages from within the same project. |
| Execute Process Task | With the help of this task, you can specify command line parameters. |
| File System Task | It performs manipulations in the file system. Like moving, renaming, deleting files, and creating directories. |
| FTP Tasks | It allows you to perform basic FTP functionalities. |
| Script Task | This is a blank task. You can write NET code which performs any task; you want to perform. |
| Send Mail Task | You can send an email to notifying users that your package has is finished, or some error occurs. |
| Bulk Insert Task | Use can loads data into a table by using the bulk insert command. |
| Script Task | Runs a set of VB.NET or C# coding inside a Visual Studio environment. |
| Web Service Task | It executes a method on a web service. |
| WMI Event Watcher Task | This task allows the SSIS package to wait for and respond to certain WMI events. |
| XML Task | This task helps you to merge, split, or reformat any XML file. |
Основные возможности SQL Server Integration Services
- С помощью SSIS можно осуществлять простой импорт и экспорт данных из одного источника в другой. Для этих целей Microsoft даже разработал специальный отдельный инструмент, т.е. своего рода «Мастер», который выполняет эти действия максимально просто. О нем мы разговаривали в материале «Средство импорта и экспорта данных в MS Sql 2008». И если кто помнит, в одном из последних окон там предлагается сохранить все действия в пакет SSIS, для того чтобы потом его можно было редактировать и выполнять. Другими словами, все что умеет «Мастер импорта и экспорта» можно автоматизировать через пакет SSIS;
- Автоматизация простого импорта и экспорта — это конечно хорошо, но службы Integration Services способны гораздо на большее. Допустим, Вам необходимо осуществить сбор информации (выгрузить данные) из нескольких источников, разных, т.е. например, из MS SQL Server, из какой-то базы Access, из парочки текстовых файлов и других. Затем необходимо эти данные преобразовать, объединить, отфильтровать и разбить по назначению, например, каждому пользователю нужно предоставить только ту часть информации, за которую отвечает именно он. А потом все это выгрузить, причем в том формате, который удобен конкретному пользователю, к примеру, одному удобно работать в Excel, а другой, продвинутый пользователь, хочет видеть эти данные в Access. И в итоге разложить эти файлы по папкам, которые указали пользователи. Все это можно сделать внутри одного пакета SSIS. И этот пакет можно расширять, все зависит от Ваших потребностей. Вот другая ситуация, когда SSIS будет очень полезен и сэкономит достаточно много времени, допустим, Вам необходимо организовать заполнение некого хранилища данных на постоянной основе из различных источников данных. SQL Server Integration Services поддерживает очень много разных источников данных, с которыми он может установить связь, например, конечно же, вышеуказанные SQL Server, базы Access, файлы Excel, XML, текстовые файлы и другие (подробней об этом можно почитать в электронной документации по SQL Server);
- В пакет SQL Server Integration Services можно включать задачи по запуску исполняемых файлов (exe) или пакетных файлов (bat) что, как Вы понимаете, позволяет автоматизировать задачи вообще не связанные с импортом и экспортом данных. Например, у Вас возникла необходимость запустить одну программу, которая будет выполнять некие вычисления, а когда она завершит свою работу запустить другую программу, а когда и она завершится, то третью ну, в общем, возможность запускать программы из пакета есть, а как использовать ее решать, конечно, Вам;
- SQL Server Integration Services позволяет выполнять задачи связанные с обслуживанием баз данных, т.е. функции администрирования. Для этих целей в SSIS даже создан специальный блок «Задачи плана обслуживания», в который включены такие задачи как: резервное копирование базы дынных, сжатие базы данных, очистка журнала, обновление статистики, перестроение индекса и другие. Другими словами, если у Вас есть подобные задачи, и они у Вас не автоматизированы, Вы можете автоматизировать их, используя пакеты SSIS;
- С помощью служб Integration Services можно получить доступ к объектам и данным SQL сервера и соответственно выполнять над ними операции, например, вставки, удаления или изменения данных. Другими словами, из пакета SSIS можно выполнять SQL инструкции или хранимые процедуры;
- SSIS умеет работать и с файловой системой, т.е. выполнять операции связанные с файлами и каталогами, например, создавать, копировать, перемещать или удалять файлы и папки. Допустим, Вам необходимо автоматизировать процесс копирования каких-то файлов на постоянной основе, это можно сделать как с помощью простых bat файлов, так и с помощью пакетов SSIS.
- В SQL Server Integration Services есть возможность отправлять сообщения электронной почты. Например, Вы хотите получать письмо об успешном или неуспешном завершении той или иной задачи.
Как Вы понимаете это далеко не полный список возможностей служб Integration Services, но главной возможностью и особенностью является то, что все вышеперечисленное можно комбинировать в одном пакете. Иными словами, Вы можете создать пакет SSIS, в котором допустим, Вы сначала делаете резервную копию базы данных, а также других источников (например, файлов Excel). Затем запускаете хранимую процедуру, которая обновляет данные в базе SQL сервера, потом осуществляется выгрузка данных из всех этих источников, происходит их объединение и доставка этих данных в удобном формате пользователю, а для того чтобы пользователь узнал, что его данные подготовлены отправить ему письмо по электронной почте. По моему мнению, возможностей SSIS более чем достаточно.
Основные возможности SQL Server Integration Services
- С помощью SSIS можно осуществлять простой импорт и экспорт данных из одного источника в другой. Для этих целей Microsoft даже разработал специальный отдельный инструмент, т.е. своего рода «Мастер», который выполняет эти действия максимально просто. О нем мы разговаривали в материале «Средство импорта и экспорта данных в MS Sql 2008». И если кто помнит, в одном из последних окон там предлагается сохранить все действия в пакет SSIS, для того чтобы потом его можно было редактировать и выполнять. Другими словами, все что умеет «Мастер импорта и экспорта» можно автоматизировать через пакет SSIS;
- Автоматизация простого импорта и экспорта — это конечно хорошо, но службы Integration Services способны гораздо на большее. Допустим, Вам необходимо осуществить сбор информации (выгрузить данные) из нескольких источников, разных, т.е. например, из MS SQL Server, из какой-то базы Access, из парочки текстовых файлов и других. Затем необходимо эти данные преобразовать, объединить, отфильтровать и разбить по назначению, например, каждому пользователю нужно предоставить только ту часть информации, за которую отвечает именно он. А потом все это выгрузить, причем в том формате, который удобен конкретному пользователю, к примеру, одному удобно работать в Excel, а другой, продвинутый пользователь, хочет видеть эти данные в Access. И в итоге разложить эти файлы по папкам, которые указали пользователи. Все это можно сделать внутри одного пакета SSIS. И этот пакет можно расширять, все зависит от Ваших потребностей. Вот другая ситуация, когда SSIS будет очень полезен и сэкономит достаточно много времени, допустим, Вам необходимо организовать заполнение некого хранилища данных на постоянной основе из различных источников данных. SQL Server Integration Services поддерживает очень много разных источников данных, с которыми он может установить связь, например, конечно же, вышеуказанные SQL Server, базы Access, файлы Excel, XML, текстовые файлы и другие (подробней об этом можно почитать в электронной документации по SQL Server);
- В пакет SQL Server Integration Services можно включать задачи по запуску исполняемых файлов (exe) или пакетных файлов (bat) что, как Вы понимаете, позволяет автоматизировать задачи вообще не связанные с импортом и экспортом данных. Например, у Вас возникла необходимость запустить одну программу, которая будет выполнять некие вычисления, а когда она завершит свою работу запустить другую программу, а когда и она завершится, то третью ну, в общем, возможность запускать программы из пакета есть, а как использовать ее решать, конечно, Вам;
- SQL Server Integration Services позволяет выполнять задачи связанные с обслуживанием баз данных, т.е. функции администрирования. Для этих целей в SSIS даже создан специальный блок «Задачи плана обслуживания», в который включены такие задачи как: резервное копирование базы дынных, сжатие базы данных, очистка журнала, обновление статистики, перестроение индекса и другие. Другими словами, если у Вас есть подобные задачи, и они у Вас не автоматизированы, Вы можете автоматизировать их, используя пакеты SSIS;
- С помощью служб Integration Services можно получить доступ к объектам и данным SQL сервера и соответственно выполнять над ними операции, например, вставки, удаления или изменения данных. Другими словами, из пакета SSIS можно выполнять SQL инструкции или хранимые процедуры;
- SSIS умеет работать и с файловой системой, т.е. выполнять операции связанные с файлами и каталогами, например, создавать, копировать, перемещать или удалять файлы и папки. Допустим, Вам необходимо автоматизировать процесс копирования каких-то файлов на постоянной основе, это можно сделать как с помощью простых bat файлов, так и с помощью пакетов SSIS.
- В SQL Server Integration Services есть возможность отправлять сообщения электронной почты. Например, Вы хотите получать письмо об успешном или неуспешном завершении той или иной задачи.
Как Вы понимаете это далеко не полный список возможностей служб Integration Services, но главной возможностью и особенностью является то, что все вышеперечисленное можно комбинировать в одном пакете. Иными словами, Вы можете создать пакет SSIS, в котором допустим, Вы сначала делаете резервную копию базы данных, а также других источников (например, файлов Excel). Затем запускаете хранимую процедуру, которая обновляет данные в базе SQL сервера, потом осуществляется выгрузка данных из всех этих источников, происходит их объединение и доставка этих данных в удобном формате пользователю, а для того чтобы пользователь узнал, что его данные подготовлены отправить ему письмо по электронной почте. По моему мнению, возможностей SSIS более чем достаточно.
Особенности
SSIS Импорт / Экспорт Мастер позволяет пользователю создавать пакеты , которые перемещают данные из одного источника данных до места назначения без каких — либо преобразований. Мастер может быстро перемещать данные из различных типов источников в различные типы назначения, включая текстовые файлы и другие экземпляры SQL Server.
Разработчики, которым поручено создавать или поддерживать пакеты SSIS, используют инструмент визуальной разработки на основе Microsoft Visual Studio, который называется SQL Server Business Intelligence Development Studio (BIDS). Он позволяет пользователям редактировать пакеты SSIS с помощью пользовательского интерфейса с перетаскиванием. Среда сценариев для написания программного кода также доступна в инструменте. Пакет содержит множество элементов, определяющих рабочий процесс. После выполнения пакета инструмент обеспечивает мониторинг в реальном времени с цветовой кодировкой. (Примечание: в более поздних версиях MS SQL Server функция BIDS была заменена на «SQL Server Data Tools — Business Intelligence» (SSDT-BI). )
- Подключения
- Подключение включает в себя информацию, необходимую для подключения к определенному источнику данных. Задачи могут ссылаться на соединение по его имени, что позволяет изменять или настраивать детали соединения во время выполнения.
- Обработчики событий
- Рабочий процесс может быть разработан для ряда событий в различных областях, где они могут произойти. Таким образом, задачи могут выполняться в ответ на события в пакете, такие как очистка после ошибок.
- Параметры (службы интеграции SQL Server 2012)
- Параметры позволяют присваивать значения свойствам внутри пакетов во время выполнения пакета. Вы можете иметь параметры проекта и параметры пакета. В общем, если вы развертываете пакет с использованием модели развертывания пакета, вам следует использовать конфигурации вместо параметров.
- Ограничения приоритета
- Задачи связаны ограничениями приоритета. Ограничение приоритета, предшествующее конкретной задаче, должно быть выполнено до ее выполнения. Среда выполнения поддерживает параллельное выполнение задач, если это позволяют ограничения их приоритета. В противном случае ограничения могут допускать разные пути выполнения в зависимости от успеха или неудачи других задач. Вместе с задачами ограничения приоритета составляют рабочий процесс пакета.
- Задания
- Задача — это атомарная рабочая единица, которая выполняет какое-то действие. В коробке есть несколько десятков задач, начиная от задачи файловой системы (которая может копировать или перемещать файлы) до задачи преобразования данных. Задача преобразования данных фактически копирует данные; он реализует функции ETL продукта
- Переменные
- Задачи могут ссылаться на переменные для хранения результатов, принятия решений или влияния на их конфигурацию.
Пакет может быть сохранен в файл или в хранилище с иерархическим пространством имен в экземпляре SQL Server. В любом случае содержимое пакета сохраняется в XML .
После завершения конструктор также позволяет пользователю начать выполнение пакета. После запуска пакет можно легко отлаживать или отслеживать.
Особенности задачи потока данных
SSIS предоставляет следующие встроенные преобразования:
| Агрегирование | Столбец экспорта и импорта | Вращаться |
| Аудит | Для контейнера цикла | Количество строк |
| Преобразование кеша | Контейнер цикла по каждому элементу | Выборка строк |
| Копировать / Карта | ( Нечетко ) Поиск | Компонент скрипта |
| Конверсия данных | Нечеткая группировка | Медленно меняющееся измерение |
| Обучение модели интеллектуального анализа данных | Команда OLE DB | Срок извлечения |
| Запрос интеллектуального анализа данных | Обработка разделов | Поиск по сроку |
| Производный столбец | Процентная выборка | Отменить поворот |
| Обработка измерений | Преобразование выборки строк |
Преобразование «Условное разбиение» используется для условной маршрутизации строк к другим объектам преобразования на основе определенного условия. Она похожа на конструкцию » » в языке C.
SSMS and BIDS
SSMS provides different options to develop your SSIS package starting with Import and Export wizard with which you can copy the data from one server to the other or from one data source to the other. With these wizards, we can create a structure on how the data flow should happen and make a package and deploy it based on our needs to execute in any environment.
Business Intelligence Development Studio (BIDS) is a tool which can be used to develop the SSIS packages. BIDS is available with SQL Server as an interface which provides the developers to work on the work flow of the process that can be made step by step. Once the BIDS is installed with the SQL Server installation, we can locate it and start our process as shown in the steps below.
Action 2 : Extract the list of defined connection and connection providers
|
1 |
<xssimpleTypename=»BasePropertyNameEnum»> <xsrestrictionbase=»xsstring»> <xsenumerationvalue=»Description»> <xsenumerationvalue=»DTSID»> <xsenumerationvalue=»CreationName»> <xsenumerationvalue=»ObjectName»> <xsrestriction> <xssimpleType> <xssimpleTypename=»ConnectionManagerPropertyNameEnum»> <xsunionmemberTypes=»DTSBasePropertyNameEnum»> <xssimpleType> <xsrestrictionbase=»xsstring»> <xsenumerationvalue=»DelayValidation»> <xsrestriction> <xssimpleType> <xsunion> <xssimpleType> <xssimpleTypename=»ConnectionManagerObjectDataPropertyNameEnum»> <xsrestrictionbase=»xsstring»> <xsenumerationvalue=»Retain»> <xsenumerationvalue=»ConnectionString»> <xsenumerationvalue=»FileUsageType»> <xsenumerationvalue=»Format»> <xsenumerationvalue=»LocaleID»> <xsenumerationvalue=»Unicode»> <xsenumerationvalue=»HeaderRowsToSkip»> <xsenumerationvalue=»HeaderRowDelimiter»> <xsenumerationvalue=»ColumnNamesInFirstDataRow»> <xsenumerationvalue=»RowDelimiter»> <xsenumerationvalue=»DataRowsToSkip»> <xsenumerationvalue=»TextQualifier»> <xsenumerationvalue=»CodePage»> <xsenumerationvalue=»ServerName»> <xsenumerationvalue=»UseFile»> <xsenumerationvalue=»UseEncryption»> <xsenumerationvalue=»RetainData»> <xsrestriction> <xssimpleType>
|
Let’s review some fields that I find interesting for further lookup.
First of all, there is a Retain property that you can set to « True » if you want to share this connection accross multiple tasks in a given package. It means that if it’s not set to « True », SSIS will create a new connection everytime it has to use it.
The ConnectionString property is the most interesting one as it’s the actual descriptor of the connection.
Finally, there are two base properties ObjectName and Description that can be useful
This leads to the design of this temporary table :
|
1 |
CREATETABLE#StagingPackageConnStrs ( PackageUniqifierBIGINTNOTNULL, DelayValidationVARCHAR(100), ObjectNameVARCHAR(256), ObjectDescriptionVARCHAR(4000), RetainVARCHAR(100), ConnectionStringVARCHAR(MAX) );
|
And here is the T-SQL statement we can use to populate this table, based on the content of previous #SSISPackagesList
temporary table.
|
1 |
WITHXMLNAMESPACES ( ‘www.microsoft.com/SqlServer/Dts’ASpNS1, ‘www.microsoft.com/SqlServer/Dts’ASDTS )— declare XML namespaces INSERTINTO#StagingPackageConnStrs ( PackageUniqifier, DelayValidation, ObjectName, ObjectDescription, Retain, ConnectionString ) SELECTPackageUniqifier, CASE WHENSSIS_XML.value(‘./pNS1:Property ‘,’varchar(100)’)= THEN’False’ WHENSSIS_XML.value(‘./pNS1:Property ‘,’varchar(100)’)=-1 THEN’True’ ELSESSIS_XML.value(‘./pNS1:Property ‘,’varchar(100)’) ENDASDelayValidation, SSIS_XML.value(‘./pNS1:Property’, ‘varchar(100)’)ASObjectName, SSIS_XML.value(‘./pNS1:Property’, ‘varchar(100)’)ASObjectDescription, CASE WHENSSIS_XML.value(‘pNS1:ObjectData/pNS1:ConnectionManager/pNS1:Property’,’varchar(MAX)’)=THEN’True’ WHENSSIS_XML.value(‘pNS1:ObjectData/pNS1:ConnectionManager/pNS1:Property’,’varchar(MAX)’)=-1THEN’False’ ELSESSIS_XML.value(‘pNS1:ObjectData/pNS1:ConnectionManager/pNS1:Property’,’varchar(MAX)’) ENDASRetain, SSIS_XML.value(‘pNS1:ObjectData/pNS1:ConnectionManager/pNS1:Property’,’varchar(MAX)’)ASConnectionString FROM#SSISPackagesListPackageXML CROSSAPPLY PackageXMLContent.nodes ( ‘/DTS:Executable/DTS:ConnectionManager’ )ASSSIS_XML(SSIS_XML) ; |
Well, as you can see above, to get data about connections, you « CROSS APPLY » with XML nodes of type ‘/DTS:Executable/DTS:ConnectionManager’.
It’s not difficult to understand that you will do the exact same thing to extract information about either package configuration or variables :
- For configurations, nodes will be of type ‘/DTS:Executable/DTS:Configuration’.
- For variables, nodes will be of type ‘/DTS:Executable/DTS:Configuration’.
Microsoft SQL Server Integration Services (SSIS) Features Table
See Full Table
| Features | Microsoft SSIS |
|---|---|
| Deployment | SSIS: on-premises or cloud |
| Azure Integration Services: cloud | |
| System Requirements | |
| Operating System | Windows or Linux |
| Processor | 1.4 GHz x64 for Windows |
| dual-core 2 GHz x64 for Linux | |
| RAM | 1GB Windows |
| 2 GB Linux | |
| Storage | 6 GB |
| Software | .NET Framework for Windows |
| Connectors | for ActiveX Data Objects, Excel, flat files, FTP, webservers, message queues, ODBC, OLE DB, mail servers, others |
| Design and Development Environment | Graphical environment |
| Key Capabilities | |
| ELT | Yes |
| ETL | Yes |
| CDC | Yes |
| Data Quality | Yes through SQL Server |
| Data Governance | Yes through SQL Server |
SEE ALL
BIG DATA ARTICLES
Create SSIS package
In order to create SSIS basic package, you need to
-
Create a project for the package.
-
Add a control flow and data flow to the package.
-
Add components to the data flow.
What is SSIS?
-
SSIS is an ETL tool for data warehousing that comes with Microsoft SQL Server. There is no extra cost for SSIS services. It lets you setup automated data load or extract processes to and/or from your SQL Server.
-
-
SSIS stands for SQL Server Integration Services and ETL stands for Extract-Transform-Load. It is comparable to other ETL tools like Informatica and IBM Datastage etc.
What does it do?
-
SSIS provides you platform referred as SSDT to develop ETL solution which could be the combination of one or more packages.
-
Solutions are saved with sln extension and packages are basically XML files saved with dtsx extension.
-
Packages are deployed in SQL Server msdb database called as SSISDB and managed in Integration Services Catalog in SSMS (SQL Server Management Studio).
Creating Package
1. Go to File => New => Project, name your project. Click OK.
It will open SSIS designer. On left hand panel you will see SSIS toolbox with all the tasks and at the center you will see various tabs to switch between control flow, data flow, parameters, event handler and package explorer. On right panel you will see solution explorer where you can find connection manager.
Now, in order to create a SSIS package you need at-least one control flow and a data flow task. Data flow task is simply a task which is used to Extract, Load and Transform the data and control flow is like the logical unit which controls the execution of tasks, like the flow in which tasks will execute.
2. Drag and drop drop data flow task from SSIS toolbox to central panel (control flow tab) like below,
3. You can double click on Data Flow Task to rename it. I am keeping it as default «Data Flow Task». Now right click on Data Flow Task => Edit, or you can simply select the «Data Flow Task» and click on «Data Flow» tab, it will open screen where you can edit your «Data Flow Task».
4. Now drag and drop OLE DB Source, OLE DB Destination and Data Conversion task from SSIS toolbox to designer space as shown below,
5. Select OLE DB Source and drag and drop the blue/green arrow to connect Data Conversion task. And similarly drag and drop/green blue arrow (not the red one) from Data conversion task to OLE DB Destination. Like this,
6. Now, you need to create OLE DB connection for source and target. For this go to Solution explorer panel on right hand side => Connection manager => New connection manager. Select OLE DB and click ADD.
If you have already created OLE DB data connection earlier on your machine it will show up here, otherwise you can click on NEW and create new one.
Just enter your database name and test connection. I assume you have AdventureWorks database running on your machine if not please refer this post.
I have already installed SQL Server 2014 and SQL Express so you can see 2 instances of SQL Server service running on my machine.
Now, for example I have chosen AdventureWorks2014 database.
7. Now go back to Data Flow screen and right click on OLE DB Source task => Edit. Choose a sample table from drop down list, ..
Now go to Columns tab, remove selected columns and select these five columns — Name, ListPrice, Size, Weight and SellStartDate and click OK. It’s just for example purpose.
8. Now go to Data Flow and right click on Data Conversion task => Edit. Select SellStartDate column and change its data type from to , keep alias name same and click OK. Just a minor datatype conversion to showcase this example.
9. Now, right click on OLE DB destination editor => Edit => New
SSIS by default creates the «create table» statement for you with input columns.
CREATE TABLE (
nvarchar(50),
money,
nvarchar(5),
numeric(8,2),
datetime,
date
)
Edit the table name and remove and hit OK,
CREATE TABLE (
nvarchar(50),
money,
nvarchar(5),
numeric(8,2),
date
)
Mappings should look like this, just click OK.
10. Now right click on the blue/green arrow between Data conversion task and OLE DB Destination task and enable data viewer. This is not a mandatory step but just to see data preview after data conversion.
11. Now hit START button on top of your screen. This will start the package.
You can see SellStartDate has only date after conversion (no time field), all the tasks are green ticked that means they ran successfully and number of rows 1,008 processed. You can stop the flow or restart again from buttons highlighted on top of the screen.
That’s it. This package creation example was showcased by Microsoft itself. I haven’t modified anything to keep example simple and informative.
I hope you enjoyed the post. If you have any question please mention in the comments section below. Thank you.
