Merry Christmas to everyone out there. I hope you all had fun and kept yourselves from trouble during this wonderful season of the year. Today, I want to continue the discussion of one of Microsoft’s BI tool used for most ETL processes,SSIS. We covered some basics and l want to jump right into creating our first SSIS package. To successfully get along and understand this lesson, it is recommended that you have Business Intelligence Development Studio(BIDS) installed. I went through how to get the tool installed in the earlier tutorials so I will now assume that you have BIDS installed and you know how to start it.
When you start BIDS and come to the start page, go to FILE->NEW->PROJECT. Select Integration Services Project and name the project “MyFirstSSISProject”. Let it be stored in the default location and check mark the option to create direct for the solution. Here is the screen shot of all that l said. Click OK when done.
You will then be brought to front page of the solution created, with a default package created with the name “package”. Note the extension for any SSIS package is .dtsx, so you see package.dtsx opened up in the designer. The Designer is the interface where you build your ETL Strategy, which I’ve covered most of the components in the earlier tutorial, starting with Control flow, Data flow and Event Handlers. When you arrive at the Designer for the first time, you land on the Control Flow, which is the highest level for the package. Now, lets rename this package to something like myFirstSSISpackage. To do this, click on VIEW-> SOLUTION EXPLORER. The Solution Explorer panel will open on the right. Right click “package.dtsx” , under SSIS Packages folder select rename in the drop down menu.(To expand the SSIS packages folder, click + sign and click – sign to collapse it). Click inside the highlighted field and rename the package as proposed above. Make sure the extension part is retain as it is. So the name will be MyFirstSSISPackage.dtsx. After giving the name, click any where outside and a dialog box will show up asking if you want to rename the package object as well, click yes and you will see that the new name is shown at the top of the designer as well. Now, our first package today is a simple one. Let us assume we have data in one database that we want to load to another database, say on the same server or different servers. This is a simple requirement and can be done in the following way. First, we need to know our source and destination servers, databases and tables. I’m assuming I’m moving data from one database on my desktop server to another database on my desktop server, so this is going to be easy, but its the same approach to use for any type of circumstance. Once you know the source and destination, make sure you have access to perform the ETL strategy. If you do not have access to a server or a database, you can not perform any ETL process on it. I assume I have access to the databases on my desktop server (duuuuuuhhh). For simplicity, Lets use Adventureworks database and Lets create one more database to move data into it. Lets call it, MyETLDb. Please note, you can’t create a database in SSIS, you either use SQL or easily do it using Microsoft SQL Server Management Studio. I assume you can create a database but if you can’t, please refer to the SQL tutorials on the blog to learn little bit of TSQL to help you accomplish this task. Now, my intention is to move all the data in HumanResources.Employee table in the Adventureworks2008R2 to my new database that I called MyETLDb. I want a table to be created in my new database with the same name as the one l’m pulling from the Adventureworks database. You can accomplish this in two ways, first is to create the table in the MyETLDb to have the same name and structure before performing your ETL process or you can perform ETL and create the table at the same time in SSIS. I’m going with the second option of performing the ETL and creating the table at the same time. Here is a snapshot of how my localhost looks from Microsoft SQL Server Management Studio.
Now, l’m going to create my package to load the data from the Employee table in the Adventureworks database, create the same table structure in the MyETLDb database and dump all the Employee information there. To do this, we need to proceed by using a Data Flow Task at the Control Flow level of the package. Go To VIEW->TOOLBOX. The tool box shows up on the left side which gives you access to many tools to accomplish almost any ETL process. You notice the Data Flow Task under the Control Flow Items section. Use the – and + signs to expand and collapse any of the main sections. Here is a snapshot of the look of the expanded Control Flow Items in my ToolBox.
Lets continue by double clicking or dragging the Data Flow Task to the Design Region. We can rename it by Right clicking and choosing rename. Give it a name that can tell you what it does, for example, “Load Employees From AdventureWorks to MyETLDb”.
Snapshot of mine looks like this.
Now, we are going to get a connection managers for our source and destination databases. We need one connection manager to connect to the Adventureworks database and another to connect to the MyETLDb database. From the screen shot above, we can see the connection managers window at the bottom. As the instruction says, right-click in that window and choose new OLEDB connection from the pop up menu. A new window opens as shown below.
Click on the new button at the bottom of this window and specify the server and the database for this connection manager.
Here is a screen shot of the settings to connect to the Adventureworks database on your local machine.
From the snapshot, it should be obvious what to do. The provider for the OLEDB Connection we chose is automatically selected as Native OLE DBSQL Server Native Client 10.0. In the server name, either simply enter a period (.) or type the word localhost, those refer to the local instance of SQL server running on your system. If you have multiple instances of SQL server running on your local machine, then you have to specify the name of the specific instance you are connecting. If you are connecting to any other server, just type the connection name for that server. In the Logon to server section, choose windows authentication, l hope that’s how your server is set up to automatically log in the current user of the system. On the other hand, if you have SQL Server Authentication set up, then choose that option and finally select the Adventureworks database from the drop down box under connect to database section. Click on the Test Connection button and hope to receive the message, Test connection Succeeded or else something is wrong with either the database, server, or your log on. Click the OK buttons and the new connection manager will show up. You can rename it at the control flow level to anything by right clicking and choosing rename. So, this is our Source Connection manager, that means this connection manager connects to the source we are pulling data from. We need one more connection manager that connects to the destination we are dumping the data, which is the MyETLDb. Go through the same process but this time select the MyETLDb database. Now that we have our Source and Destination connection managers, we are going to continue with our ETL process. Your screen should now look like below,
Now, we are going inside our Data Flow Task and define the Source and Destination for the ETL process.
Double click the Data Flow Task place in the Design Area and observe it opens up a new design area. This are is the Data Flow and you can also reach here by clicking the Data Flow tab at the top of the designer any time and selecting the name of the Data Flow that you want to build. Click to open the toolbox at this level to discover set of new tools relating to building the data flow. Here is a snap shot of what you should be looking at now, 
Under the Dataf Flow Sources, double click or drag and drop the OLEDB Source onto the Design area. Rename it to ‘Adventureworks Employees’. Now we are going to connect to the employee table using this OLEDB Source by double click it, then choose adventureworks2008r2 under the OLEDB connection manager section, choose table or view under the data access mode and select Humanresources.Employee under the Name of the table or the view section. Click the OK button to complete setting the source connection.
Now, we are going to connect to the MyETLDb, which is our Destination, so we go to the tool box and double click or drag and drop OLEDB Destination, which can be found under Data Flow Destinations. Rename it to MyETLDb Employees. As soon as you drop the OLEDB destination, there is a crossed red showing there is danger, don’t panic, we are getting there. That red warning sign means there is no input to the destination. We give the destination an input by clicking on the Adventureworks Employees, a green arrow shows below, drag the arrow to touch the MyETLDb Employees. Still the red light is showing because we have not yet define where the destination connects to and dump the input we are giving it. So Lets do that by double clicking the MyETLDb Employees. Choose MyETLDb under the OLE DB connection manager, choose table or view-fast load in the Data access mode, now under the name of the table or view, we are going to do something totally different. Remember we did not create the table that we are going to dump the data into, so we have to do it now. So under Name of the table or view, click the new button to the right of the drop down box. This opens up a window with SQL statement to create the table. Just give the table a name and click ok. Here is a screen shot.
Now click on Mappings at the left panel and make sure all columns from the source are mapped to the appropriate columns in the destination. The mapping is almost all the times done automatically if you have same column names but its required to go into the mapping to make sure everything works fine before existing. Here is a screen shot of what you should see.
Click Ok and realize everthing looks perfect for the Data Flow now. here is what you should see.
That is the end of our ETL Design, the only thing left now is to run our package to get all data from Adventureworks employee and put it in our MyETLdb. So lets go and run the package, well, there are many ways to run a package but the simplest is to either click on the green arrow that looks like a play button at the top or press F5 on the key board. So just do one of the above and see what happens. Here is a look at how it looks at my control flow level.
Here is a look at it at my Data Flow level
We can see 290 employees were loaded into MyETLDb. The green color means success while red while indicate error. The package has finished executing but you have to stop it to go back to the design mode. Click on the blue writing to switch to design mode. To verify that we have data loaded, you can go back to the MyETLDb and make sure there is data there. I hope you all had fun creating your first package.
Happy new year ! ! !
Questions, comments and suggestions welcome.
Jah Bless
Peace N Luv.
