Microsoft SQL Server 2012 Integration Services: An Expert Cookbook: Over 80 expert recipes to design, create, and deploy SSIS packages with this book and ebook

There are many components that made SSIS a powerful tool for data integration. The objects in SSIS known as Tasks can be found under the Control Flow tab in the SSIS Toolbox and they are usually referred to as Tasks or Control Flow Tasks. The Control Flow Tasks consist of a wide variety of operations, from a simple File System Task that moves files, to XML Tasks such as XSLT or XPath that work with XML data in different ways.

Control Flow Tasks are categorized into sections; re-organizing them is simple (refer to Getting started with SSDT recipe in Chapter 1, Getting Started with SQL Server Integration Services). Each task has an editor; which is a graphical user interface that is useful for SSIS Developers. Some of the most important properties of a Task can be configured in its editor.

A good SSIS Developer should be familiar with the use of all Control Flow Tasks and use them in appropriate situations. In this chapter you will see an example of each Control Flow Task with...

One of the most useful tasks in SSIS is the Execute SQL Task. This task can be used for executing every T-SQL command on an underlying database. Underlying databases can vary because there is a wide variety of connection managers that have SQL Task support. In this recipe, we will see some of the most useful scenarios such as the execution of T-SQL commands and fetching results, or passing input and output parameters.

Although systems have evolved greatly in terms of technology, communications, and security, data access to source systems is often achieved through flat files. The people responsible for "Operational Systems" (OS) do not like applications that they don't fully understand pulling unknown volumes of data at unknown times from their systems. Even when someone tries to change this, they always get the same reaction: "I'm not letting you alter my system with something I don't know!" They prefer to stay in control and make data sources available in flat files on some 'staging area', in spite of directly opening the OS 'doors'. Therefore, reading text files and subsequent treatment, is a widely used practice mainly in a Data Warehousing scenario known as the PUSH model. In this approach, the OS starts data extraction to an external repository (usually known as a staging area) that will be accessed by an ETL Process.

To handle these file and folder...

The FTP Task gives us the ability to send files from a local system to a remote host or retrieve them from a remote host to a local system. There are other types of operations such as creating a remote folder or local folder. This task is very handy because FTP is in use between many companies for sharing their data. A company may export all its data into some CSV files, ZIP them and put them on an FTP address, and another company may need to get those files from FTP. The FTP Task also provides a way to work with multiple files.

The Execute Package Task executes another package (known as the child) from the current package (known as the parent). This parent-child approach is fundamental:

It's not recommended to try to do all your work inside one package; the project should be divided into several components that can work together and be called by a parent package.

This recipe demonstrates the logic inherent to an Extract, Transform, and Load (ETL) project. ETL is a project that, by nature, is divided into three stages: the first stage is responsible for extracting data from sources, the second stage is responsible for the...

One of the handiest tasks in SSIS Control Flow is the Execute Process Task. With this task we can run any other application that has an executable file. The only thing to consider is that the executable file should be run in silent mode because SSIS isn't made for interaction with users. In this recipe, we compress and decompress some files within this task with the help of 7-Zip.

Web services are in wide use nowadays as a method to interact between systems in a cross-platform environment. As web services work with XML and serialize their outputs in the XML format and also get their inputs in the XML format, they are widely used as a standard way to interconnect systems together. There are lots of benefits of using web services, but discussing all of them is beyond the scope of our book.

This recipe demonstrates the use of the Web Service Task using a free web service chosen from among several alternatives on the Internet. This web service is very simple and returns the cities from a specific country:

XML is a universal format for data on different platforms, and there are lots of companies that work with XML data and send and receive data through systems as XML files. There are some operations which can be done with XML data: validating XML files over XSD or DTD files, transforming XML data with XSLT, querying data from XML with XPath, and other operations that are supported in many applications and programming languages nowadays.

The SSIS XML Task is a powerful task that provides a few of these operations on XML data. In this recipe, we fetch data from a table as XML, validate it on an XSD file, and then transform it with XSLT.

Data Profiling is an increasingly used task, and as such, it's necessary to understand the data extracted from sources in detail and also to reduce problems with the data's quality during the data integration process. In an ETL scenario, this data profiling should be done before the ETL process accesses the source system directly, or if that's not possible, then after the Extract stage where there usually isn't any transformation on data applied. If the data profile is not known, the risk of problems during later stages (Transform and Load) will significantly increase.

When source data is profiled using this task, the output could be a column null ratio, column lengths and values distribution, column pattern, and also the candidate columns for being keys. The output of this analysis generates XML reports that can be saved to a file or an SSIS variable.

The results gathered by the Data Profiling Task are very useful for tuning a database.

Bulk Insert is used in scenarios where a large volume of data should be quickly inserted into a destination database. There are several ways to deal with this under SSIS but the difference will be in the speed of inserting such volumes of data into a database. This approach is used to ensure speed while copying data or performing transformations while the data is moving.

Some constraints should be considered before using this approach:

WMI stands for Windows Management Instrumentation and this Task provides easy access to information form the management objects of Windows-based operating systems. For example: the number of hard drivers, CPU usage statistics, a list of running processes, and any other information can be fetched with WMI. We use a language named WQL to fetch information from WMI, which is a query language for WMI.

As WMI provides extensive access to management information for our system and the operating system, SSIS provides two tasks for WMI. In this recipe, we take a look at the WMI Data Reader Task and read the running processes' information and load its data into a file.

In the previous recipe, one of the most common scenarios was reading system information from WMI. SSIS provides another WMI Task named WMI Event Watcher Task, which is more useful than the Data Reader Task. WMI Event Watcher Task provides a way to watch for a WMI event. This awesome feature will make the SSIS package very powerful. As WMI provides extensive information for the management of objects, interaction with this information as events will be very useful. Scenarios such as watching a folder for new files or watching CPU for 50 percent usage are some of the most common scenarios that can be accomplished with this task.

In this recipe we will watch a folder for new files, and if a new file comes into the folder, we will raise a message box after the WMI Event Watcher Task.

Transferring SQL database objects and data between databases is often needed in the real-world. For example, some scenarios could be the creation of the database environment along with multiple environments such as development, testing, and production; and for example, to create temporary tables to assist the ETL process.

This recipe demonstrates how we can simply transfer all database objects and data between the Adventure Works case sample and a new and empty database created for this recipe.