PraCtICE modifying an existing Data movement
ExErCIsE 2 Edit the SSIS package Created by the SQL Server Import and Export Wizard
1. Open the TK463_IEWizard.dtsx package by double-clicking it in the Solution Explorer.
2. Review the control flow of the package. It should contain two tasks: an Execute SQL Task named Preparation SQL Task 1, and a data flow task named Data Flow Task 1, as shown in Figure 3-19.
figure 3-19 The control flow of the SSIS package created in Lesson 1.
3. Double-click (or right-click) Preparation SQL Task 1, and in the shortcut menu select Edit to open the Execute SQL Task Editor. As shown in Figure 3-20, the editor provides access to the Execute SQL Task’s settings used in configuring the operation.
figure 3-20 The Execute SQL Task Editor.
You will learn more about this task in Chapter 4; in this exercise, you just need to re-view the SQL statement.
4. To see the entire definition, click the ellipsis button inside the value box of the SQL-Statement property. Resize the script editor dialog box, shown in Figure 3-21, for bet-ter readability, and review the T-SQL script.
As you can see, the task will attempt to create two tables without checking first to see whether they already exist. The failure will not affect the destination database; it will, however, affect the execution of the SSIS package, causing it to fail.
figure 3-21 T-SQL script generated by the Import and Export Wizard.
5. Close the SQL Statement script editor dialog box by clicking Cancel. For the purposes of this exercise, the code does not need to be modified in any way.
6. Close the Execute SQL Task Editor window by clicking Cancel once more.
7. Right-click Preparation SQL Task 1 and select Properties on the shortcut menu. In the lower right of the IDE, you can see the Properties pane, displaying additional settings for the selected object—in this case, the Execute SQL Task. Find the FailPackageOn-Failure setting and make sure its value is False, as shown in Figure 3-22.
figure 3-22 The Execute SQL Task properties.
This will prevent the possible (or rather, probable) failure of Preparation SQL Task 1 from failing the entire SSIS package.
REAL WORLD (Dis)allowing Failure
The purpose of the workaround used in this exercise is to illustrate a point. In actual development work, you should be very careful about when to ignore the failure of individual operations. Focus on preventing failure, rather than exposing your solutions to unpredictability.
8. Select the precedence constraint (the arrow) leading from Preparation SQL Task 1 to Data Flow Task 1. Press Delete on the keyboard or right-click the constraint and select Delete to remove the constraint.
Precedence constraints are discussed in more detail in Chapter 4.
9. From the SSIS Toolbox, drag another Execute SQL Task onto the control flow pane.
10. Double-click the newly added task, or right-click it and then select Edit, to open the Execute SQL Task Editor. Configure the task by using the information in Table 3-1.
tabLe 3-1 Execute SQL Task Properties
property value
Name Preparation SQL Task 2
ConnectionType OLE DB
Connection DestinationConnectionOLEDB
SQLSourceType Direct input
11. Click the ellipsis button inside the value box of the SQLStatement property to edit the SQLStatement, and type in the statements from Listing 3-1.
Listing 3-1 Truncating Destination Tables
TRUNCATE TABLE Production.ProductAndDescription;
TRUNCATE TABLE Production.ProductModelInstructions;
Optionally, you can copy and paste the statements from the TK463Chapter03.sql file, located in the C:\TK463\Chapter03\Code folder. Click OK when you are done editing the statements.
12. When you have finished configuring the task as defined in steps 10 and 11, confirm the changes by clicking OK. Figure 3-23 shows the configured Preparation SQL Task 2.
figure 3-23 Preparation SQL Task 2.
13. Select Preparation SQL Task 1. A tiny arrow should appear below it. Drag the arrow over to Preparation SQL Task 2, and then release it to create a precedence constraint between the two tasks, as shown in Figure 3-24.
figure 3-24 Creating a precedence constraint.
14. Double-click the precedence constraint you just created, or right-click it and select Edit. In the Precedence Constraint Editor, shown in Figure 3-25, you can configure the conditions of the SSIS package execution.
figure 3-25 The Precedence Constraint Editor.
15. Review the options available for the constraint, make sure that Constraint is selected as the evaluation operation, and then select Completion as the new value, as shown in Figure 3-25. Confirm the change by clicking OK.
NOTE Evaluation opErations
Precedence constraints are not the only available technique that can be used to control the conditions of SSIS execution. Additional techniques are discussed in Chapter 6,
“Enhancing Control Flow.”
16. Select Preparation SQL Task 2, and connect it to Data Flow Task 1 with a new prece-dence constraint. Leave the constraint unchanged. Figure 3-26, shows the amended control flow.
figure 3-26 Modified control flow.
17. Double-click Data Flow Task 1, or right-click it and select Edit, to view its definition, as shown in Figure 3-27.
figure 3-27 The definition of Data Flow Task 1.
You can observe two data flows, extracting the data from two views in the source database and loading it into two tables in the destination database.
For now, simply observe the data flow definition. You will learn more about data flow programming in Chapter 6.
REAL WORLD Combining vs. isolating Data Flows
In practice, you will rarely see multiple data flows sharing the same data flow task.
Although it may seem logical to place data flows that constitute the same logical unit into a single data flow task, it might be more appropriate for maintenance and auditing purposes to place each data flow into its own data flow task.
When done, return to the control flow view by selecting Control Flow at the top of the SSIS package editor.
18. Save the SSIS project, but keep it open, because you will need it in the next exercise.
ExErCIsE 3 Configure the Connections and run the SSIS package in Debug Mode 1. At the bottom of the SSDT IDE, locate the Connection Managers pane, which provides
access to the connection managers used by your SSIS package. There should be two connection managers, as shown in Figure 3-28.
figure 3-28 The Connection Managers pane.
Both connection managers were created by the Import and Export Wizard that you used in Lesson 1 of this chapter.
2. Double-click the SourceConnectionOLEDB connection manager icon, or right-click it and then select Edit, to open the connection manager editor. This editor provides access to the connection manager settings; depending on the type of connection, dif-ferent variants of the editor are available. The connection managers in this project use the OLE DB data provider.
3. Review the connection properties, as shown in Figure 3-29, and think about which of them would have to be modified for production (Provider, Server Name, authentica-tion, and/or database name).
figure 3-29 The OLE DB connection manager.
Here, if you want, you can select the All tab to view more settings and think about what others you have used in the past that would also differ between development and production. This is especially useful if you have worked with SSIS (or Data Transfor-mation Services) in earlier versions of SQL Server.
When done, click Cancel to close the editor. No changes to the connection manager are necessary at this time.
4. Right-click the SourceConnectionOLEDB icon, and then select Parameterize from the shortcut menu. In the Parameterize window, shown in Figure 3-30, you can create new parameters or assign existing parameters to any of the exposed properties of the selected object. Parameterization allows the settings to be modified after the solution’s deployment, without requiring a re-design.
figure 3-30 OLE DB connection manager parameterization.
5. Select the ServerName property to be parameterized first; use the Create New Param-eter option with the default values to create a new paramParam-eter for the OLE DB connec-tion’s server name, and leave the rest of the settings unchanged.
When done, click OK to complete the operation.
6. Repeat the process in steps 4 and 5 for the same connection manager, this time pa-rameterizing the InitialCatalog property.
7. After you finish parameterizing the SourceConnectionOLEDB connection manager, repeat steps 4 through 6 for the DestinationConnectionOLEDB connection manager.
8. After parameterizing both connection managers, save the SSIS solution, and then open the Parameters tab of the SSIS package pane, as shown in Figure 3-31.
figure 3-31 SSIS Package Parameters
REAL WORLD Parameterization Considerations
Not all settings of all of the various objects that can exist in an SSIS package can be parameterized. If there are settings that you need to allow to be configured, but that are not supported by SSIS parameterization, you could try using a generic property that is exposed to SSIS parameterization but that also includes the setting you are trying to parameterize. (For example, network packet size is not exposed to parameterization, but it can be set inside the connection string, which can be parameterized.)
9. When done, return to the Control Flow tab.
10. On the Debug menu, select Start Debugging, or press F5 on the keyboard, to run the package in debug mode.
11. When the package runs, you can observe the order of the operations’ execution, gov-erned by the control flow. As each task is completed, it is marked with a completion icon: a green check mark shows successful operations, whereas a red X marks failed ones. Figure 3-32 shows the result of the execution.
figure 3-32 SSIS execution in debug mode.
Preparation SQL Task 1 failed, as expected, because it attempted to create two tables that already existed in the destination database, but because of a completion prece-dence constraint instead of the (default) success constraint, and because of a disabled setting that would otherwise cause the package to fail, the rest of the tasks as well as the package itself completed successfully.
12. After the debugging execution has completed, close SSDT.
Lesson Summary
■
■ Existing SSIS packages can be added to SSIS projects in SQL Server Data Tools (SSDT).
■
■ Control flows contain the definitions of data management operations.
■
■ Control flows determine the order and the conditions of execution.
■
■ SSIS package settings can be parameterized, which allows them to be changed without direct access to SSIS package definitions.
Lesson review
Answer the following questions to test your knowledge of the information in this lesson. You can find the answers to these questions and explanations of why each answer choice is correct or incorrect in the “Answers” section at the end of this chapter.
1. The Execute SQL Task allows you to execute SQL statements and commands against the data store. What tools do you have at your disposal when developing SSIS pack-ages to develop and test a SQL command? Choose all that apply.
a. SQL Server Management Studio (SSMS) B. SQL Server Data Tools (SSDT)
C. The Execute SQL Task Editor
D. SQL Server Enterprise Manager (SSEM)
2. You need to execute two data flow operations in parallel after an Execute SQL Task has been completed. How can you achieve that? (Choose all that apply.)
a. There is no way for two data flow operations to be executed in parallel in the same SSIS package.
B. You can place both data flows inside the same data flow task and create a prece-dence constraint leading from the preceding Execute SQL Task to the data flow task.
C. You can create two separate data flow tasks and create two precedence constraints leading from the preceding Execute SQL Task to each of the two data flow tasks.
D. You can create two separate data flow tasks, place them inside a third data flow task, and create a precedence constraint leading from the preceding Execute SQL Task to the third data flow task.
3. Which precedence constraint can you use to allow Task B to execute after Task A even if Task A has failed?
a. The failure precedence constraint, leading from Task A to Task B.
B. The success precedence constraint, leading from Task A to Task B.
C. The completion precedence constraint, leading from Task A to Task B.
D. Use two precedence constraints—a success precedence constraint, and a failure precedence constraint, both leading from Task A to Task B.
case scenarios
In the following case scenarios, you apply what you’ve learned about creating SSIS pack-ages. You can find the answers to these questions in the “Answers” section at the end of this chapter.