SQL Server Integration Services (SSIS) Walkthrough

summary

The purpose of this document is to provide an overview of using SQL Server 2005 Integration Services to extract data from a MUMPS database using the KB_SQL ODBC driver.

requirements

• KB_SQL Server V4.x (or greater)
• KB_SQL ODBC Driver V4.x (or greater)
• Microsoft SQL Server 2005 (or greater)

overview

From the computers “Start” menu launch the SQL Server Business Intelligence Studio as seen in Figure 1.

Figure 1. The SQL Server Business Intelligence Studio application

Once within the application you will want to create a Integration Services project by selecting the “New” sub menu item off of the main “File” menu as seen in Figure 2.

Figure 2. New project creation dialog.

After your project has been completed you will be presented with the project design environment. The first step in designing your project is to create a data source. To do this right-click the “Data Sources” folder located in the “Solution Explorer” and click the “New Data Source” menu item as seen in Figure 3.

Figure 3. The New Data Source solution explorer menu item.

Once you have selected the “New Data Source” menu item you will then be presented with the “Data Source Wizard” as seen in Figure 4.

Figure 4. The Data Source Wizard.

Once within the “Data Source Wizard” click the next button to bypass the introductory screen. On the next screen of the wizard you we will create a new connection definition by selecting the “New” button as seen in Figure 5.

Figure 5. The connection definition screen.

After clicking the “New” button on the connection definition screen you will then be presented with the “Connection Manager” as seen in Figure 6. This is where we will select the provider and specify the properties for the provider as seen in Figure 6. In this walkthrough we will select the “Odbc Data Provider”, select the system Odbc data source for KB_SQL and, set the user name/password fields that are valid for your database.

Figure 6. The connection manager.

After clicking the “Ok” button you will then need to click the “Next” button followed by the “Finish” button on the final screen of the “Data Source Wizard” to complete the creation of the of the data source.

Once your data source has been completed your next step will be to add a connection to the SSIS package. To do so right-click in the “Connection Managers” area of the design environment and select the “New Connection From Data Source” menu item as seen in Figure 7. Making this selection will present you with the “Select Data Source” dialog box as seen in Figure 8; which you will select the KB_SQL data source we just created.

Figure 7. Connection Managers.

Figure 8. Select Data Source dialog.

Once your connection has been added to your SSIS package you will now want to begin designing and adding data flow objects to your package. To do so, we will first start by selecting the “Data Flow” tab which can be found directly next to the “Control Flow” tab we have currently been working with.

Once on the “Data Flow” tab you will first want to start by adding a data flow source object. In this walkthrough we will use the “DataReader Source” object which can be found within your projects “Toolbox” as seen in Figure 9.

Figure 9. DataReader Source.

Just as we did with the “Data Flow Task” object on the “Control Flow” tab you will drag and drop the “Data Reader Source” object directly from the “Toolbox” out to your design palette as seen in Figure 9.

Once the “Data Reader Source” object is available on your palette we will then want to right-click the object and select the “Edit” menu item. This will display the advanced editor dialog for this specific object as seen in Figure 10.

Figure 10. Advanced Editor for source object.

On the main tab of the advanced editor you will click on the “Connection Manager” cell to display a drop down list of all available connections within this package. From this drop down list you will want to select the connection (i.e., KB_SQL) we created earlier in this walkthrough.

After you have made your connection selection our next step will be to select the “Component Properties” tab. On this tab you will need to find the property “SqlCommand” as seen in Figure 11.
Figure 11. Advanced Editor Component Properties.

In this walkthrough we will use a sample table entitled “Employees” which ships with the KB_SQL Server product. For the “SqlCommand” property we have specified a simple select (i.e., SELECT * FROM EMPLOYEES) statement to return all the rows and columns from the employees table as seen in Figure 11. After you have set the “SqlCommand” you can click the “Ok” button to return to the design environment.

Next, we will now want to add a destination object. In this walkthrough we will use the “Flat File Destination” object as seen in Figure 12. Just as we did with the “DataReader Source” object you will drag and drop the destination object from the “Toolbox” directly to your design palette as show in Figure 13.

Figure 12. Flat File Destination object.

Figure 13. Flat File Destination object on palette.

Once you have the destination object on your palette you will next want to connect the source object to the destination object. To do this you will right-click the “DataReader Source” object and select the “Add Path” menu item.

Figure 14. Right-click source object menu to “Add Path” to destination object.

After clicking the “Add Path” menu item you will then be presented with the “Data Flow” dialog box as shown in Figure 15. Within this dialog box you will need to set the “From” (i.e., DataReader Source) and “To” (i.e., Flat File Destination) drop down menu’s to their appropriate values.

Figure 15. Data Flow dialog.

After clicking the “Ok” button on the “Data Flow” dialog you will then be presented with the “Input Output Selection” dialog as seen in Figure 16. Within this dialog you will want to set the “Output” drop down list to the “DataReader Output” item and click the “Ok” button.

Figure 16. Input Output Selection.

Once back in the main design environment window you will next need to right click on the “Flat File Destination” object and select the “Edit” menu item. You will then be presented with the “Flat File Destination Editor” as seen in Figure 17.

Figure 17. Flat File Destination Editor.

Within this dialog window we will next need to create a connection for the flat file object; this is done by clicking the “New” button within this window. You will then be presented with the “Flat File Format” dialog box as seen in Figure 18.

Figure 18. Flat File Format dialog.

For the purposes of this walkthrough we will select the “Delimited” option and select the “Ok” button. Making this selection will then present you with the “Flat File Connection Manager” dialog as seen in Figure 19.

Figure 19. Flat File Connection Manager Editor.

Within this dialog on the “General” section you will want to click the “Browse” button to identify where you would like the destination file to be placed as seen in Figure 19. Please note, in this walkthrough we will be creating a Unicode .CSV file with column headers entitled “Employee.” Once you have setup the “General” section with the appropriate file settings you can click the “Ok” button which will return you to the “Flat File Destination Editor” dialog. From here you will need to click the “Mappings” section so the source and destination objects can sync with each other to provide suggested auto mappings as seen in Figure 20.

Figure 20. Mappings section.

After the appropriate mappings have been setup you are now safe to click the “Ok” button to return back to the main design environment window. Finally, at this point you are now ready to execute your newly created SSIS package. To execute the package you can simply right-click anywhere on the design palette and click the “Execute Task” menu item as seen in Figure 21.

Figure 21. Execute Task menu item.

If you package executes appropriately both the source and destination objects should light up in green as seen in Figure 22.

Figure 22. Successful package execution.