Working with SQL Server Integration Services
SQL Server Integration Services (SSIS) is a set of tools that let you transfer data to and from SQL Server 2005. In this lab, you’ll work with the SQL Server Business Intelligence Development Studio to develop and run a SSIS transform. When done, you will have transferred a database table from the AS/400 to SQL Server 2005.
Create a new database in SQL Server
___ Open the Microsoft SQL Server Management Studio. Connect to the SQL Server database server that is configured for this course.
___ In the SQL Server Management Studio, expand the server name, then expand the Databases item. Right-click on the Databases item and select the New Database menu item as shown in Figure 1.
ssis001 Figure 1: Select the New Database option to create a test database in SQL Server.
___ In the New Database dialog (Figure 2), enter a unique name for the database. Leave all of the other options set to their defaults and click the OK button.
___ Verify that your database is listed in SQL Server, as shown in Figure 3. Expand the Tables item to verify that there are no tables currently in your test database.
ssis003 Figure 3: Verify that your database is in SQL Server.
___ You can leave the SQL Server Management Studio program open. Minimize it so that it is not displayed on your Windows desktop.
Start a SQL Server Integration Services Project
___ Locate the SQL Server Business Intelligence Development Studio program as shown in Figure 4. If you don’t have the program installed on your PC, you will need to install it from the SQL Server 2005 DVD in the “Tools” section.
ssis011
___ The Business Intelligence Development Studio opens a version of Visual Studio 2005, as shown in Figure 5.
ssis012 Figure 5: The Business Intelligence Development Studio opens a version of Visual Studio 2005.
___ Click the Create Project item as shown in Figure 5 to start a new project.
___ In the New Project dialog (Figure 6), select Business Intelligence Projects, Integration Services Project. Enter a name and location for your project, then click the OK button.
___ Visual Studio now displays your project in the Solution Explorer, as shown in the upper right corner of Figure 7. The design surface is set to use the tools shown in the Toolbox (left side), which are specifically designed to work with SQL Server data tasks.
ssis014 Figure 7: Visual Studio now displays your project in the Solution Explorer (upper right) and opens the design surface and toolbox for this type of project.
Create connections to your AS/400 and SQL Server
___ Locate the Connection Managers pane near the bottom of the Control Flow design surface. Right- click within that pane and select the New OLE DB Connection item, as shown in Figure 8.
___ In the Configure OLE DB Connection Manager dialog (Figure 9), click the New button.
ssis016 Figure 9: Click the New button to add a new OLE DB Connection Manager.
___ In the Connection Manager dialog (Figure 10), click the Native OLE DB\SQL Native Client and select the IBMDASQL OLE DB Provider, as shown in the figure. Click the OK button to close the selection list.
ssis017 Figure 10: Select the IBMDASQL OLE DB Provider as the type of OLE DB provider for the connection to the AS/400.
___ Back in the Connection Manager (Figure 11), specify the name of your AS/400 system. Enter your AS/400 user name and password, then click the Test Connection button. You should see a message box indicating a successful connection. Close the message box, then click OK to exit the Connection Manager.
ssis018 Figure 11: Enter the server name of the AS/400 and your user ID and password. Click the Test Connection button to verify the connection.
___ Back in the Configure dialog (Figure 12), verify that a connection was added for your AS/400.
Click the New button again to add the SQL Server connection.
ssis019 Figure 12: Verify that your AS/400 connection is added. Click the New button to add a connection for SQL Server.
___ Verify that the SQL Native Client provider is selected in the Connection Manager, as shown in Figure 13. Click the drop-down list and select the SQL Server from the list/
ssis020 Figure 13: Verify that the Provider is SQL Native Client, then select the SQL Server name.
___ Select or enter the name of your SQL Server database, as shown in Figure 14.
ssis021 Figure 14: Select the test database you created and test the connection.
___ Click the OK button to close the Connection Manager.
___ Verify that you have two data connections, as shown in Figure 15. Click the OK button to close the dialog.
ssis022 Figure 15: Verify that your two connections are listed, then click the OK button.
___ Verify that the two connections are now shown in the Connection Managers panel in Visual Studio, as shown in Figure 16.
ssis023 Figure 16: Back in Visual Studio, verify that the two connections are listed in the Connection Managers panel.
Add a Data Flow Task to the project
___ Verify that the Control Flow tab is the currently selected tab (at the top of the Visual Studio design surface). In the Toolbox, locate the Data Flow Task tool in the Control Flow Items section. When you hover the mouse over the tool, you will see a description of the tool, as shown in Figure 17.
ssis024 Figure 17: Locate the Data Flow Task tool in the Control Flow Items section of the Toolbox.
___ Drag-and-drop a Data Flow Task tool onto the Control Flow design surface, as shown in Figure 18.
ssis025 Figure 18: Drag-and-drop the Data Flow Task onto the design surface.
Add work items to the Data Flow task
___ Click the Data Flow tab at the top of the design surface, as shown in Figure 19. Note that when you click the tab, the Toolbox changes to show tools that are used with that type of task.
ssis031 Figure 19: Click the Data Flow tab to switch the design surface.
___ Locate the OLE DB Source in the Data Flow Sources section of the Toolbox, and drag-and-drop the tool onto the Data Flow design surface, as shown in Figure 20.
ssis032 Figure 20: Drag-and-drop the OLE DB Source tool onto the design surface.
___ Double-click on the OLE DB Source icon in the design surface to open the OLE DB Source Editor, as shown in Figure 21.
___ Verify that the Connection Manager item is selected (left-side column). Select your AS/400 OLE DB connection manager, specify the Data access mode as SQL command, then enter the
following for the SQL command text:
select * from qiws.qcustcdt
ssis033 Figure 21: Select the OLE DB connection manager that you created for the AS/400, set Data access mode to SQL command and enter the SQL command text.
___ After entering the SQL command, click the Preview button. You will see the data that is in the AS/400 table. Close the Preview window after viewing the data.
___ Click the Columns item in the left-side column (Figure 22). You will see the list of columns in the database table that will be used in the data transfer. If you don’t want to transfer a column, remove the check mark from the list of columns.
ssis034 Figure 22: Click the Columns item to display the list of database columns that will be used.
___ Click the OK button to close the OLE DB Source Editor dialog.
___ In the Toolbox, scroll down to the Data Flow Destination section (Figure 23). Locate the OLE DB Destination tool and drag-and-drop it onto the design surface.
ssis035 Figure 23: Drag-and-drop an OLE DB Destination from the Data Flow Destinations section of the Toolbox.
___ If the message box shown in Figure 24 is displayed, click the Yes button.
ssis036 Figure 24: Click the Yes button when this message is displayed.
___ In the design surface, click the OLE DB Source icon to select it (Figure 25). You should see two arrows appear below the icon.
ssis041 Figure 25: Click the OLE DB Source to select it. A connection arrow (green arrow) appears below the icon.
___ Drag-and-drop the green (connection) arrow from the OLE DB Source to the OLE DB Destination.
When you drop the arrow onto the Destination, you will see a connecting line, similar to that shown in Figure 26.
ssis042 Figure 26: Drag the connection arrow and drop it onto the OLE DB Destination.
___ Double-click on the OLE DB Destination icon to open the OLE DB Destination Editor (Figure 27).
Select the connection manager for the SQL Server, specify Table or View as the Data access mode, then click the New button for the table.
ssis043 Figure 27: In the OLE DB Destination Editor, verify that the SQL Server OLE DB connection manager is selected, Table or view is selected for Data access mode, then click the New button for the table.
___ In the Create Table dialog, change the table name to [QCUSTCDT] as shown in Figure 28. Click the OK button after making the change.
ssis045 Figure 28: Change the table name to QCUSTCDT, then click the OK button.
CAUTION: when you click the OK button, the table is created in SQL Server. If you entered the table
___ Click the Mappings item in the left-side column (Figure 29). You should see that all of the columns in the source table (AS/400) are mapped to corresponding columns on the destination table (SQL Server).
ssis046 Figure 29: Click the Mappings item to view the column mappings between the source and destination tables.
___ Click the OK button to close the OLE DB Destination Editor dialog.
Run the Data Transform
___ Back in Visual Studio, click the Start Debugging icon (Figure 30) or press F5 to start running the data transform.
ssis047 Figure 30: Click the Start Debugging icon to run the data transfer.
___ Because you are working with a small database file, the transform should run quickly. Visual Studio changes to show you the run-time environment as shown in Figure 31.
ssis048
___ Click the Progress tab. You will see the results of the data transform and the total run-time, as shown in Figure 32.
ssis049 Figure 32: Click the Progress tab to view the results of running the data transfer.
___ To exit the run-time environment, you can click the link shown in Figure 33 or you can click the Debug, Stop Debugging menu item.
ssis050 Figure 33: Click the link to return to design mode.
___ Run the data transfer again.
Add a Control Flow item to clear the table before the transfer
___ Now go to the SQL Server Manager Studio. Locate your database and the table. Right-click the table name and select the Open Table menu item. You will see the data that was copied from the AS/400 in your table, as shown in Figure 34. If you ran the transfer twice, you will see that the data is duplicated.
ssis051 Figure 34: Go to the SQL Server Management Studio. You should now see the QCUSTCDT table in your database.
Right-click the table and select the Open Table option to view it.
___ Go back to Visual Studio. Click the Control Flow tab as shown in Figure 35.
ssis061 Figure 35: Back in Visual Studio, click the Control Flow tab.
___ In the Control Flow Items section of the Toolbox, locate the Execute SQL Task item and drag- and-drop it onto the design surface, as shown in Figure 36.
ssis062 Figure 36: Drag-and-drop the Execute SQL Task icon onto the design surface.
___ Right-click the Execute SQL Task icon and select the Edit item from the pop-up menu, as shown in Figure 37.
ssis063 Figure 37: Right-click the Execute SQL Task icon and select the Edit menu item.
___ In the Execute SQL Task Editor dialog (Figure 38), click the Connection item and select the SQL Server connection.
___ In the SQLStatement item, enter the following statement:
DELETE FROM QCUSTCDT
ssis064 Figure 38: Select the connection to your SQL Server database. Enter an SQL DELETE statement to delete all rows from the SQL Server QCUSTCDT table.
___ Click the OK button to close the Execute SQL Task Editor.
___ Back in the design surface, click the Execute SQL Task icon to select it. Drag-and-drop its
connection arrow (green arrow) to the Data Flow Task icon, as shown in Figure 39. This indicates that the SQL task is to be run before the Data Flow task.
ssis065 Figure 39: Drag-and-drop a connection from the Execute SQL Task to the Data Flow Task.
___ Run the data transform again (click the Start icon or press F5). After the transform completes, go to SQL Server Management Studio and open your table. You should see that it has only one set of
Add a Data Viewer to the Data Transform data flow
Although you usually don’t need to monitor the data flow as it is happening, you can add a data viewer to a data transform task. By adding a viewer, you can see the data as it is being transferred. This can help you debug data transforms when you create a new transform.
___ Click the Data Flow tab to open its design surface. Right-click the connection between the OLE DB Source and the OLE DB Destination and select the Data Viewers item as shown in Figure 40.
ssis071 Figure 40: Right-click the connection between the Data Flow icons and select the Data Viewers item.
___ In the Data Flow Path Editor (Figure 41), be sure the Data Viewers item is selected in the left column, then click the Add button.
___ In the Configure Data Viewer dialog (Figure 42), select the Grid type, then click the OK button.
ssis073 Figure 42: Select the Grid type of data viewer.
___ Back in the design surface, you will see a small icon for the data viewer, as shown in Figure 43.
ssis074 Figure 43: The data viewer icon is now shown as part of the data flow.
___ Start the data transform again. This time, you will see a small floating window in the run-time view, similar to that shown in Figure 44. You will not be able to view the contents of the window until it is “docked”. When you grab the window and move it, Visual Studio displays “docking stations” (blue icons with arrows). Drop the window onto one of the docking stations and release the mouse. The viewer window attaches to the position in Visual Studio nearest the docking station.
ssis075 Figure 44: When you run the data transfer, the data viewer may appear as a floating window. Move it to one of the
"docking points" shown in Visual Studio and drop it on the docking point.
___ Figure 45 shows the data viewer docked near the top of Visual Studio. You can see the rows of data that were transferred.
ssis076 Figure 45: You can now see the rows that are transferred when you run the data transfer.
