Preface

Microsoft SQL Server 2012 Integration Services: An Expert Cookbook is a complete guide for everyone, from a novice to a professional in Integration Services 2012. SQL Server Integration Services is an ETL tool, which stands for Extract Transform and Load. There is a need for a data transfer system in all operational systems these days, and SSIS is one of the best data transfer tools. In this book, all aspects of SSIS 2012 are discussed with lots of real-world scenarios to help readers to understand usage of SSIS in every environment.

What this book covers

Chapter 1, Getting Started with SQL Server Integration Services, provides an overview of the ETL concepts and ETL terminologies, why ETL is needed in the technology world, and what problems ETL will solve. Then an overview of SSIS as an ETL tool is provided to help readers to get an overall view of the other parts of the book.

Chapter 2, Control Flow Tasks, explores all Control Flow Tasks with real-world samples of each Task. The reader will learn what each Task stands for, what is its usage, real-world scenarios, and the new tasks available in SSIS 2012.

Chapter 3, Data Flow Task Part 1—Extract and Load, explains the data sources and data destinations under the Data Flow Task. Data Flow Task is the most functional part of SSIS, to which an SSIS Developer probably dedicates most time.

Chapter 4, Data Flow Task Part 2—Transformations, explores the transformations used to apply data quality and business rules that are essential to prepare data loaded into destinations. Data Flow Task provides an easy way to transform source data into the form needed by its destination in several different ways.

Chapter 5, Data Flow Task Part 3—Advanced Transformation, briefly discusses Advanced Transformations. In real-world scenarios, different data sources don't provide the same structure, so there is a need to unify them in a unique structure. There are some transformations in SSIS Data Flow Task that use complex ways to apply such changes on data stream. We call them Advanced Transformations.

Chapter 6, Variables, Expressions, and Dynamism in SSIS, describes how SSIS works with dynamism with the aid of expressions, what are the limitations of some tasks in dynamism, and what are the alternative solutions. SSIS as an executable unit needs to have a structure for declaring in-memory variables and store some data in memory to pass between Tasks through the execution phase. Besides the variables, there is a built-in statement language in SSIS components and Tasks to do many operations such as data conversion, data splitting based on a condition, or creating text filenames based on date. In this chapter, readers will learn how to work with variables and expressions in many scenarios. Dynamism is the most powerful aspect of an ETL tool in data transfer operations.

Chapter 7, Containers and Precedence Constraints, explains three types of containers and precedence constraint in the SSIS Control Flow, which help developers to control the flow of task execution. All of these containers and the precedence constraints are covered in this chapter with real-world samples.

Chapter 8, Scripting, explains the powerful aspect of SSIS: scripting—developers can use scripting whenever other tasks or transformations can't help them to fulfill their requirements. There are two places for scripting in SSIS the—Script Task in Control Flow and Script Component in Data Flow. Scripting in both of these components will be covered in this chapter with samples.

Chapter 9, Deployment, describes how to deploy the developed packages and projects to a production environment, discussing different methods of deployment with the pros and cons of each way in real-world scenarios.

Chapter 10, Debugging, Troubleshooting, and Migrating Packages to 2012, explains the ability of SSIS to debug and troubleshoot like all robust systems. Developers need to know how to face problems in Control Flow or Data Flow, how to handle errors in Data Flow Task, and troubleshoot them. Debugging and troubleshooting have two sides in SSIS—Control Flow and Data Flow. This chapter describes both sides with appropriate examples. Also, this chapter has two recipes on migrating packages from the previous versions to 2012.

Chapter 11, Event Handling and Logging, explores all aspects of event handlers in SSIS besides logging in custom or built-in modes. SSIS provides a set of handlers for events on executable objects of Control Flow, which helps developers to handle these events and design appropriate operations on them. These event handlers also help developers to do some custom logging in their packages. There is a built-in logging feature in SSIS which can be used in general logging scenarios.

Chapter 12, Execution, covers different methods of package execution, and the properties and settings that can be configured at the time of execution.

Chapter 13, Restartability and Robustness, covers all these aspects of SSIS: SSIS has the structure to get input parameters from other applications. On the other hand, Packages can operate in a restartable mode. They can store their state at the time of failure and continue execution from that state next time. They are also capable of running Tasks in packages as a transaction.

Chapter 14, Programming SSIS, explains library classes for creating package and tasks, configuring them, deployment of a package, and running the package. Integration Services provide a set of .NET library classes and methods to do all parts of SSIS lifecycle operations from .NET programming.

Chapter 15, Performance Boost in SSIS, covers recommendations and best practices for raising the performance of packages and Data Flow. As an advanced part of each tool, there are some tips to raise the performance; they are described in this chapter.

What you need for this book

You need to have Microsoft SQL Server 2012 Business Intelligence Edition for running all recipes of this book.

Visual Studio 2010 is also needed for Chapter 14, Programming SSIS, which is about creating SQL Server Integration Services packages programmatically; so if you want to read and practice all the recipes in this book it is necessary to have Microsoft Visual Studio 2010.

Who this book is for

If you are a SQL database administrator or developer looking to explore all the aspects of SSIS and need to use SSIS in the data transfer parts of systems, then this is the best guide for you. Basic understanding of working with SQL Server Integration Services is required.

Conventions

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: "This Data Flow reads some customer data (first name and last name) from an Excel file, applies some common transformations and inserts the data into an SQL table named SalesLT.Customer."

A block of code is set as follows:

<title>The First Book</title>
<title>Becoming Somebody</title>
<title>The Poet's First Poem</title>

When we wish to draw your attention to a particular part of a code block, the relevant lines or items are set in bold:

      <xsd:element name="genre"    type="xsd:string"/>
      <xsd:element name="price"
        type="xsd:float" minOccurs="0" maxOccurs="unbounded" />
      <xsd:element name="pub_date"
        type="xsd:date" minOccurs="0" maxOccurs="unbounded" />
      <xsd:element name="review"   type="xsd:string"/>

Any command-line input or output is written as follows:

x "C:\SSIS\Ch02_ControlFlowTasks\R03_FTP Task\LocalFolder\files.7z"

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in the text like this: "If any error occurs while executing the process, the error can be stored into a variable with the StandardErrorVaraible option".

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply send an e-mail to <[e-mail]>, and mention the book title via the subject of your message.

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

Errata

Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in the text or the code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration and help us improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the errata submission form link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata will be uploaded on our website, or added to any list of existing errata, under the Errata section of that title. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.

Piracy

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works, in any form, on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at <[e-mail]> with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.

Questions

You can contact us at <[e-mail]> if you are having a problem with any aspect of the book, and we will do our best to address it.