I’ve started talking about SSIS in the last tutorial and today, l’m moving forwared to introduce the Interface of the SSIS platform. Its very important to know the tool from outside before you start messing around with it. So, Lets go through some of the features on the interface as we start to create our first project.
When Bids is started, it brings you to the interface l put up in the previous tutorial. To create a new project, click File ->New->Project. A pop up window shows option to create a Business Intelligence project or other, but in our case, we are going to work only with Business Intelligence, so click on Business Intelligence in the project types panel and choose integration services project from the template panel on the right. Give the Project a name and select where to store the files, normally, l like to store it at the default location, which is C:UsersusernameDocumentsVisual Studio 2008Projects.
The solution and name of the project will be the same by default. Check mark the option to create directory for the project to create the new directory for the project. Hit the OK button to create your first SSIS Project, but don’t be too fast, its empty and you have long way to get something in there. That’s why we are doing this so lets go get it.
The first screen you get is shown below. As you can see, there are four tabs and each of these tabs takes you to different screen. Its good to get familiar with some important terminologies in this session before moving forward. I’ll start with the term package. This is the most common term you’ll ever hear when you work as an SSIS developer.
So what is a package? An SSIS package is the highest-level object within an SSIS application. A package is a discrete unit of work that you define for ETL operations or SQL Server Services administration operations or both. It is a collection of SSIS process control components and their objects that define the operations, process dependencies, and sequence flow of activities and operations required for a data integration application. Yeah, I know this sound gibrish if you are not familiar with this term so to make life a little easier, just remember, you create a package to carry out your Extraction, Transformation and Loading process, which is the main use of an SSIS Applicatioin. I believe l mentioned the use of SSIS in the first tutorial but if you don’t get it right, the tool is used simply to extract data, transform that data to a desired from and load it to a target destination. For example, lets say you have students register in an excel sheet but one day, you decide to store this information in SQL Server. There are many reasons why you would want to do that, example for easy access. So SSIS will help you move that data in the spreadsheet to the database with ease. It will allow you extract the data from the source, transform the data to a from you may want the data to be in the target and helps you load it to the database. So this comes to form what is called ETL.
The next terminology to familiar ourselves with is control flow, which is the opened tab showing in the image below. So what is the control flow? Control flow is an engine that manages the flow or execution of tasks and containers within a package. All the package executable are found in the control flow. Control flow is where we arrange our executable to run in the order we want them to. As you can see below, this interface allow you to add all the necessary executable and containers in the order of your choice to accomplish your ETL task. Another close terminology of concern next to control flow will be the data flow task. What is a data flow task?
It is a task which enables you to extract and transform data from various sources and load them to an array of destinations such as relational databases, flat files, and XML. The data flow task also allows you to apply several out of the box transformations as well as custom transformations to your data. This task is the primary task in any ETL strategy as well as package when populating data warehouses and data marts. We will continue the termininologies in the next section but our last one for this section is Event handlers.
An Event Handler is a flow that responds programmatically to events that occur on Package level, Container level, Task level, and Variable-Value Change level. There are different types of events such as onpreexecute, onpostexecute, onerror, etc. The event handler allows you to define what action to take when any of these events occur. The event handler interface and the data flow interface look very similar. I will be discussing more of the terminologies in the next section and get our hand at creating a basic package. Until then, Peace and Love.
