Course: SQL Server Integration Services (SSIS) 2014

SQL Server Integration Services is part of the Microsoft suite of SQL Server Business Intelligence tools or BI Tools, which also includes Analysis Services and Reporting Services. Integration Services is Microsoft’s Enterprise grade tool for the extraction, transformation and loading or ETL of data. It’s designed to handle the migration and manipulation of data through the life cycle of a BI application and is a critical part of moving data in to data warehouses that is used in BI. But has usefulness far beyond BI applications. Any time you need to move data from one storage source to another, particularly if you need to change the data along the way, consider using Integration Services. In this course you’ll learn about what Integration Services is good for and explore its architecture and components, you’ll learn the basics of control and data flows. As well as how to make a connection to varies data sources and destinations. Then you’ll learn about two of the main ways to create packages, import and export wizard, as well as how to build them from scratch using SQL Server Data Tools, which is Microsoft’s replacement of the old Business Intelligence Studio or BIDS. You’ll also see a couple of ways to execute packages, both within data tools as well as using the execute package utility.

Control Flows are the backbone of an Integration Services package. They contain the procedural logic that dictates the flow of execution through a package, such as grapping files from a remote server, cleaning out tables in a staging server, validating the files, kicking off the data flows to get the data where it needs to go, and cleaning up afterwards. Each Integration Services Package contains a single logical control flow. That can be as simple a single task or as complex as you need it to be. The Control Flow manages everything that happens in an Integration Services package. In this course you’ll learn about the various elements in a control flow, including tasks, containers, and precedents constraints. Then you’ll learn how to add tasks and configure them using both common elements as well as the unique features of each task. Next the course explores some of the tasks that you likely to use most often with some demos of their capabilities. We’ll wrap things up with a look at precedents constraints with both determine the order at which tasks execute as well as how they define overall work flow of your Integration Services package.

The data flow task is a special control flow task that moves data from a data source to a data destination, optionally transforming the data in various ways as it moves. It is so important and complex that unlike any other control flow tasks, the data flow task has its own designer in SQL Server Data Tools. This is where you’re likely to spend most of your time when developing any non-trivial Integration Services package that moves data rather than just performs other control flow tasks. As a data flow moves data from a source to a destination, you can perform various data flow transformations on that data. These transformations tasks let you look up data from an external source change the contents of a field to upper case sort the data, merge the data flow with other flows, and much more. The data flow task is the single most important task in a control flow and performs the majority of the work in an ETL, Extract, Transform and Load, an ETL process. In this course you’ll learn about the various components that you can use to build the data flow. Including the various data sources and destinations that you can use to read data and store it in its final resting place. Then you’ll learn about the transformations that you can use to modify the data and its scheme as it moves through the data flow pipeline. You can change a fields data type, modify its contents, split the data flow in to multiple pipelines, merge data flows, perform various kinds of look up, and much more. Data flows are key part of Integration Services and its well worth the time getting to know them well.

Control flow containers provide ways to group tasks so that they execute together. Containers allow you to group control flow tasks for execution, to group tasks for repeated execution given some condition, to repeatedly execute group tasks for each data member in some kind of collection and to easily set properties that affect all its contained tasks. In the looping containers you can control how and whether the member tasks of each execution group interact with each other or to share the same variable scope defined for that container. They also provide a way to define success and failure through precedents constraints for the groups of tasks as a whole. In this course you’ll learn about all containers you can use including the mostly invisible task host container that Integration Services creates for you automatically any time that you add a task to a control flow. We’ll explore all the containers and you’ll see various demos that show how you can take advantage of them. A transaction is a core concept of relational data base systems. It is one of the major mechanisms through which a data base server protects the integrity of data by making sure the data remains internally consistent. SQL Server has always had rich support for transactions and Integration Services hooks into that support. You’ll see how to implement transaction for both a package as well as for smaller units using containers. With smart transaction implementation you can build remarkably robust packages.

Trivial Integration Services packages can take data straight from a data source and dump it unchanged into a destination. That’s certainly a valued use of a package but it doesn’t take advantage of all that you can do to data as it moves from place to place. More often you’re likely to join data for multiple sources together into a single data flow. For example, in an online transactional source data base you might have normalized product information spread across several tables, including product, product category, product description and others. That can be an efficient structure for day to day online operations but if you need to insert the data into a database warehouse for analyses, normalized data is very inefficient. So a package you create to populate the data warehouse would need to join several sources together in to a single data flow and that’s where the join transformations are useful. In this course you’ll learn about two of the join transformations built into data flows. The Merge join transformation merges two data flows into a single output flow, performing a join operation on the data. This is the same operation you can perform in T-SQL using the inner-join or outer-join syntax in a select statement. The major difference is that the transformation operates on a stream of data coming in from the data flow pipeline. Whereas the T-SQL type of join is a set operation. The other join transformation is the Lookup transformation. This performs an inner join on its single input data flow using a data set from a source defined within the transformation instead of using another data flow. What really sets this component off is its ability to cash look up data in very flexible and powerful ways. The join transformations provide powerful features to process data in an Integration Services package and this course will get you started understanding on how to put them to use.

Integration Services variables are similar to those in your favorite programming language but they work differently in Integration Services packages. Because most of the time you aren’t writing code that can directly manipulate variables. Instead you use them to set the value of properties as part of an expression or in a script component where you write VB or C# code. They are enormously useful for sharing data throughout a package including the reuse of expressions and you can use them in almost any Integration Services object such as connection managers, control flow tasks and data flow transformations. In this course you’ll learn what you need to know to use variables and expressions. We’ll start off by exploring just what a variable is in Integration Services and how it has special properties, as well as a bit of the data types available. You’ll also see where and how you can use variables, which as it turn out is in nearly every nook and cannery of a package. Then we’ll explore how to use variables in control and data flows in a package. You’ll also learn about the important property expressions feature, which lets you use variables to set values of package component properties. You’ll begin to understand how variables can make packages amazingly dynamic, responding to data characteristics, the external environment, or just about anything else you can write in code. Next we’ll explore Integration Services Expression. Expressions are the single most important feature for creating dynamic packages that are able to modify what they do based upon almost any kind of state or environmental factor. What makes expressions particularly powerful in packages is that you can imbed them directly into the various properties of just about any object in the package or in a variable for reuse through that variables scope. Variables and expressions aren’t quite the same thing as they are in your favorite programming language but they are dynamic in nature and the ability to let

You can add script to an Integration Services package in two main ways, both of which you’ll learn about in this course. First you’ll learn how to use the Script task to add script to a package’s Control Flow. Then you’ll learn how to use the Script Component to add script to a package’s Data Flow. These two components are quite different. The Script task usually executes once in the package, or perhaps once per loop in a looping container. The Script Component executes once per row in the Data Flow, and may end up executing millions of times each time the package executes if there is that much data to process. This is why there are separate components for the Control and Data Flows: the requirements of each are quite different. As a result, the code you’ll write for each is quite different, as you’ll learn in this course.

Integration Services has a variety of tools and provides various techniques you can use to see what a task or package is doing and how it is doing it, as you’ll see in this course. You can cause the behavior or a task to change when problems arise, whether from bad data or missing resources, and you can partition a Control Flow so that a whole separate set of tasks execute only when certain kinds of events occur. You can pause package execution at pre-defined breakpoints—conceptually similar to breakpoints in programming tools but quite different in practice. You cause the package to store information in a checkpoint file about where it failed so that the next time you run the package it doesn’t repeat any tasks that completed successfully. And you can log many kinds of information about the package as each task executes. In this course, you’ll learn about these debugging, error handling, and logging features that can help you diagnose most kinds of problems in your package.

More often the package or project you are working on will need to be robust enough to run repeatedly. In that case, you’ll need to deploy the package to a server and sometimes multiple servers, and Integration Services has a number of features that make this process easier, which you’ll learn about in this course. The typical lifecycle of an Integration Services package has several phases. Each phase of the lifecycle may require that you use a different set of resources for the package. Items such as file paths and connection strings will vary from phase to phase. A feature called configurations allow you to create packages that are not bound to any particular location or environment, and you’ll see how to use configurations in this course. Once your package is ready to be flexible enough to execute on various servers, you’ll need to deploy it to one or more servers. You’ll need to progress through several steps to develop packages and deploy them from one environment to another, and you’ll learn how to do that in this course.

SQL Server and Integration Services provide plenty of tools for package management that you’ll learn about in this course. These include Management Studio and SQL Server Agent, as well as command line and graphical utilities like DTUtil, DTExec, and DTExecUI. With these tools you can execute packages in various ways and move, copy, and encrypt packages. To give one idea of how flexible these tools can be, there are at least four ways to execute a package, besides SQL Server Data Tools. You’ll learn some of the most important techniques for managing packages and some of the tools you can use, including those various ways to execute packages. There is a lot of overlapping functionality, so it is important to learn enough about all the tools so that you can use the ones best suited to your preferences, environment, and work style.

In this course, you’ll start by learning about the typical threats that the security tools in Integration Services help protect against. Then you’ll learn how to control access to packages stored in the Package Store in SQL Server, about the available roles and how to create a custom role, as well as how to sign a package with a digital certificate. Then you’ll learn about the new security features that come with the SSIS Catalog introduced in SQL Server 2012, which more closely emulates the security scheme in the SQL Server database engine.