As we build larger and more powerful data centric applications, the need to bring all this information together will become more and more important. Building efficient and intelligent ETL processes will be both a requirement and a skill that will be found to be in high demand. SQL Server Integration Services (SSIS) is one such ETL tool, and because it comes with Microsoft SQL Server, it will also continue to rise in popularity.
This Virtual Class, delivered by Eric Johnson, will guide you into the world of SQL Server Integration Services. The class will start at the very basics of making a new package, and will progress towards advanced topics such as debugging and deployment. The entire goal of the course is to take you from never having used SSIS to being a competent SSIS developer in eleven sessions delivered over four weeks.
You’ll learn what SSIS is and how SSIS packages can be used to create rich and robust ETL packages. We will cover all aspects of SSIS including Control Flow and Data Flow elements. You will understand how tasks, transformations, sources, and destinations all work in concert to manipulate and move data.
The class will also explore more advanced aspects of SSIS such as scripts, expressions, debugging, deployment, and much more. By the end of the class you should be able to build, automate, and deploy packages that do what you want and run efficiently.
Eric has worked with SSIS since its release with SQL Server 2005 and before that he spent a lot of time wading around in DTS packages. He is bringing his experiences and frustrations to you so that you can learn SSIS quickly and correctly.
Session 1: The SSIS Basics – In this opening session, we will look at the structure of the class and what to expect for the next eleven sessions. We begin our tour of SSIS by looking at Business Intelligence Development Studio and the different components that make up SSIS packages. We will wrap things up by actually creating our first SSIS package.
Session 2: Control Flow Basics – In Session 1, we overviewed the different components that make up SSIS packages. In this session, we will take a detailed look at Control Flow. The Control Flow is the highest level of your packages and will manage how and in what order your package runs. We will look at tasks, containers and precedence constraints. We will end this session by looking at some suggestions for keeping your packages clean and easy to work with.
Session 3: Data Flow – Sources and Destinations – The Data Flow task will do most of the heavy lifting in SSIS packages and will usually be far more complex and detailed then anything you will do in the Control Flow. As such, the data flow will take up several sessions of this course. We will start by looking at data sources and destinations. We will cover the different types of sources and destinations, and we will talk about strategies for importing data from multiple sources of the same type. We will also take a look at things you can do to simplify working with text data sources.
Session 4: Data Flow – Transformations – Now that you are familiar with how to retrieve and store data using data flows, we will look at manipulating data with transformations. In this first of two sessions on transformations, we will look at some of the basic transformations that you will use quite frequently. Sort, Copy Column, Aggregate, Multicast, and Merge will be among the transformations we discuss.
Session 5: Advanced Transformations – In this session we will continue our look at transformations. The transformations in the session are all a bit more complex than those in session 4. Some of these are also quite common and I expect you to get a lot of use from them. We will cover Conditional Split, Lookup, Slowly Changing Dimensions, Pivot, and Unpivot.
Session 6: Advanced Data Sources and Destinations – In this session we will look at a few more advanced sources and destinations. Specifically, we will spend time with Raw File sources and destinations, and the Record-set destination. We will see how you can use a Raw File to pass data between different data flow tasks and how you can use the Record-set destination to build an in memory “queue” to iterate over rows from a table.
Session 7: Using Scripts and Variables in SSIS – Two of the most powerful features of SSIS are the ability to create your own scripts and store data into variables. In this session we will look at where and how scripts fit into SSIS packages and how variables work. We will also look at how SSIS variables can be both read and modified by scripts.
Session 8: Expressions – Expressions are enormously powerful tools in SSIS packages. They allow you to dynamically change almost anything about your package based on the situation or the data. In this session we will look at how expressions fit in and talk about some scenarios where you can use them most efficiently.
Session 9: Performing Database Maintenance – SSIS is actually the backbone of SQL Server Maintenance Plans. As such, all of the database maintenance that you can do in Maintenance Plans can also be added to standard SSIS packages. In this session, we will compare and contrast Maintenance Plans and SSIS packages and look at the various types of maintenance that can be done. We will also have a short discussion on when it is appropriate to use maintenance in your SSIS packages.
Session 10: Debugging and Troubleshooting – Developing SSIS packages is a lot like developing applications, as such you need to be able to identify and fix problems when they occur. That will be the focus of this session. We will look at data viewers, logging, setting breakpoints, viewing metadata and handling error output. We will focus the demos and discussion on what can go wrong, how to avoid it, and how to diagnose things when they do break.
Sessions 11: Deploying, Storing, and More – We have reached the top of the climb and we have certainly saved the rest for last. There is so much that you can do in SSIS that we have just really gotten into the core of SSIS; there will be a lot of things that you discover that we did not have time to cover in this class. This final session will look at the rest of the things that you should know that really didn’t fit into the other sessions. We will cover storing and deploying packages, how SSIS packages can be scheduled, running packages from the command line, and how to execute an SSIS package from Integration Services.
His background in Information Technology (IT) is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes.
He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.