Hope you have checked that the SSIS package was created in the folder you had mentioned in the last chapter. Let us understand what this package is doing and how. We will not get into all the details right now. Slowly, as we progress, you will get to understand what is happening in the package. Here, we discuss just the high-level working of the package. After this chapter, you would have a better understanding of BIDS and SSIS Package.
So before executing the package, let us have a recap of what we are trying to achieve. We are trying to load data from a text file to a SQL Server database table Input.
So before starting, let’s see what we get when we do a SELECT * FROM dbo.INPUT. Below is the output that we get. As you can see that we get an error message which tells us that the object dbo.INPUT does not exist. So we are now sure that we do not have the destination table ready before the package execution. Let’s now start with the package.
Go to the folder where you have saved the SSIS Package created in the last chapter. Right click the .dtsxfile (SSIS Package) and go to Open with. In case, you see just one option (SQL Server 2008 (or 2005) Integration Services Package Execution Utility); you need to choose default program and then select Visual Studios and then click Ok.
Or you could directly open Visual Studios and then go to File->Open -> File. Browse the SSIS package and open it. Once the package is opened, you would see the below (Fig. 3). Remember BIDS? This is BIDS with the package you created. Pay attention to the central part of the figure that is the Designer. If you see the top RED box I have highlighted, it says Control Flow (though not completely visible here). In the Control Flow, we have 2 tasks created by the Import & Export Wizard based on our settings.
Preparation SQL Task 1 and Data Flow Task.
Another important feature that I would like to draw your attention is at the bottom of the Fig. 3 that is Connection Managers. You see that we have 2 Connection managers namely DestinationConnectionOLEDB and SourceConnectionFlatFile. I will explain about them once we discuss the above tasks. Just remember that Connection Managers, as the name suggests, provides connection to the various data sources we have.
Let us see what is happening in the first task in the Control Flow “Preparation SQL Task 1”. This is the name given to the task in this package. This task is actually Execute SQL Task and you can rename it to describe its actions. Double click the task and you would see the Execute SQL Task Editor. For now, just concentrate on the parts that I have highlighted. The first thing is the property Connection and it has been set to DestinationConnectionOLEDB.
Next check the value of the property SQL Statement. If you just go to the adjoining text box you will see a blip. Click that and you will see the small window with some SQL Query written on it. In this case, it has the following query:
CREATE TABLE [dbo].[Input] (
Hope this query tells you what is going on in this task. In this task, we are just creating the destination (target) table where the data needs to be loaded.
Now just double click Cancel and come out to the Package designer. Go to the Connection managers and double click the connection DestinationConnectionOLEDB. Are the settings not exactly what you did in the Wizard earlier? This is the connection that was used in the above Execute SQL Task. So before this task is executed, the above connection manager would create the connection between the package and the Server on which it has to execute the SQL query (create table in our case). Let’s now move onto the next task in the Control flow.
Double click the next task Data Flow Task 1. As the name suggests, it is the place where the data actually flows. Having said that let us see what is happening in here, first thing to note here is that the control has moved from Control Flow to Data Flow, look at the 1st thing on top I have highlighted. We are not working in Data Flow.
Next, see the transforms that are being applied here. We have 2 transforms namely Source - Input_csv & Destination - Input. Notice that we have the same Connections available in the Data Flow as well.
Source - Input_csv: This is primarily a Flat File Source and is used to read data from flat files, be it .txt, .csv or any flat file where the data has been set in a specific format. Flat File Source can be used to read delimited files (columns in each record are separated by specific delimiter) or Fixed Width File (Files that do not have any delimiters, but have each column of fixed width so data is extracted based on the precise position of the column). We have already seen the settings in the last chapter. You could explore them by yourself.
Destination - Input: This is primarily an OLEDB Destination and is used to send final transformed data to an OLEDB Destination like SQL Server database, Access Database, Excel file, etc. You could explore this further.
Both these transforms will be discussed in detail later.
At this point of time, I will not talk about any other feature that you see in the package. You are free to explore. Now is the time to execute the package. Go back to the folder where the file is saved. Right click the .dtsx file again. Click SQL Server 2008 Integration Service Package Execution Utility and then you will see a wizard open.
Just click the button Execute on the Execute Package Utility (as in figure below). You will notice that another small window pops up where you see something like logs (Fig. 8) being written on. Scroll down and you will notice that all the tasks that we have just seen would be mentioned in the log along with the connection strings. Once the logs are written and the Close button gets enabled, you know that the execution is over. You could close the small window and the Execute Package Utility.
Now all that is left is verifying whether the package has done its work or not. Go to the figure below
When I execute the same query executed before the package execution, I get the following result and no error :)
Hence we know that the package has worked and we have the data imported from a flat file to a new table in SQL Server table.
Food for thought: BIDS too provides a way to execute the package. You can click the green play button below the menu items or press F5 key. But here in this case, you will see that the above button is disabled and F5 does not work. I leave it to you to explore why this is happening. Will talk about this in later chapter.
Note that this was not true ETL. If you remember that ETL has 3 components: Extract Transform and Load. We have seen the data being extracted from Flat File and getting loaded to the Destination. There has been no transformation done on the data.Hence I say that this is not complete ETL.
We need to learn about the various transformations available in SSIS.
Food for thought: This Package will definitely fail if you execute it again. Why??