■ practice 1 Develop an initial data movement by using the Import and Export Wizard, using views in the source data store to emulate data transformations.
■
■ practice 2 Modify the initial data movement—add proper data transformation logic as well as appropriate logic to merge new or modified data with existing data.
account for the Differences Between Development and production Environments
After a final version of a data warehousing solution has been deployed to production, any additional work on the current version, even if these development activities could in fact be reduced to “tweaking,” will eventually cause delays in the development of the next version.
With good parameterization, the burden of “tweaking” an existing solution is lifted from the shoulders of the developer and is placed on the shoulders of the administrator.
■
■ practice 1 Review your existing data movement solutions, and create a list of settings that could be beneficial to their maintenance in production.
■
■ practice 2 Create a list of elements in your data movement solution that could be parameterized, but due to their possible effect on the operational characteristics of the solution probably should not be parameterized.
answers
This section contains answers to the lesson review questions and solutions to the case sce-narios in this chapter.
Lesson 1
1. correct answers: b and D
a. incorrect: Even though this might seem like the quickest solution, it might only be quick to develop. Copying a large amount of data from the production environ-ment to a testing environenviron-ment should be avoided, especially if most of the data is just going to be discarded from the destination database afterward.
B. correct: It might appear cumbersome to design several SSIS packages for a single data movement operation, but this approach will solve the principal problem while also following good data management practices, such as avoiding unnecessary data movements.
C. incorrect: The Edit SQL option in the Column Mappings window of the Import and Export Wizard cannot be used to modify the data retrieval query, only the destina-tion table definidestina-tion.
D. correct: An SSIS package created by the Import and Export Wizard can be edited by using SSDT.
2. correct answers: b and D
a. incorrect: Dropping and re-creating tables cannot be used to merge data.
B. correct: You can use SSDT to add data merging capabilities to an SSIS package created by the Import and Export Wizard.
C. incorrect: No such option exists in the Import and Export Wizard.
D. correct: You can use SSDT to design pretty much any kind of data movement processes, especially when you want complete control over the operations needed by the process, but keep in mind that designing SSIS packages “from scratch” may not be as time efficient as possible.
3. correct answer: c
a. incorrect: SSIS package files are not stored in binary format.
B. incorrect: SSIS package files might appear as if they are saved as plain text files, but they are actually well-formed XML files.
C. correct: SSIS package files are stored in XML format; the DTSX file extension is used for distinction.
D. incorrect: SSIS packages are not Microsoft Word documents.
Lesson 2
1. correct answer: c
a. incorrect: SSDT is not an extension of SSMS. It is a stand-alone application.
B. incorrect: SSDT is not a special edition of SSMS. It is a special edition of Visual Studio.
C. correct: SSDT is a special edition of Visual Studio, with a complete database devel-opment tool set.
D. incorrect: SSDT is not a service.
2. correct answers: b and D
a. incorrect: Simple data movements can have as many data sources and as many data destinations as needed.
B. correct: Data transformations are present in complex data movements.
C. incorrect: Typically, in simple data movements, no transformations are needed, because the data is transferred unchanged. However, it is possible to transform the data at the source—such as by making retrieval queries or by using views or other similar techniques.
D. correct: Additional programmatic logic to merge source data with destination data is present in complex data movements.
3. correct answers: b and D
a. incorrect: SSIS packages cannot contain SSDT solutions.
B. correct: An SSIS project can contain as many SSIS packages as needed.
C. incorrect: An SSIS project can contain more than a single SSIS package.
D. correct: SSIS packages contain the programmatic logic used in data management operations, such as data movements and data transformations.
Lesson 3
1. correct answers: a and b
a. correct: SSMS provides all the necessary functionalities to develop and test SQL code.
B. correct: SSDT does provide a query designer; it is available from the Data menu, under Transact-SQL Editor/New Query Connection. Alternatively, the query editor can also be started from the SQL Server Object Explorer by right-clicking a data-base node, and selecting New Query... from the shortcut menu.
C. incorrect: The Execute SQL Task Editor is just a text box into which you can type or paste a SQL statement.
D. incorrect: The Enterprise Manager was replaced with SSMS in SQL Server 2005.
2. correct answers: b and c
a. incorrect: Parallel data flow execution is supported.
B. correct: You can place multiple data flow operations inside the same data flow task.
C. correct: You can, of course, place data flows in separate data flow tasks, and you can create multiple precedence constraints leading from or to the same task, as long as any two tasks are connected to each other only once.
D. incorrect: You cannot place a data flow task inside a data flow task, because it cannot contain tasks, only data flow components.
3. correct answer: c
a. incorrect: The failure precedence constraint will allow Task B to execute only if Task A has failed.
B. incorrect: The success precedence constraint will prevent Task B from executing if Task A fails.
C. correct: The completion precedence constraint will allow Task B to execute regardless of whether Task A has succeeded or has failed.
D. incorrect: Only a single precedence constraint can be used to connect two distinct tasks.
Case Scenario 1
1. An SSIS package stored in the file system, in the database, or in an unscheduled SQL Server Agent Job would be appropriate.
2. At the very least, the SSIS package would have to be parameterized so that it can be configured appropriately for the specific environment in which it is going to be used.
Additionally, the programmatic logic should account for merging new or modified data with existing data.
Case Scenario 2
1. A properly parameterized SSIS package can be redeployed and reconfigured as many times as needed, without the need for any additional development activities.
2. The network packet size property of OLE DB connections is not exposed to parameter-ization; therefore, the entire connection string would have to be parameterized.
c h a p t e r 4
I
n the previous chapter, it was established that Microsoft SQL Server Integration Services (SSIS) facilitate data movement. Of course, the functional capabilities available in SSIS are not limited to data movement alone—far from it! In its essence, SSIS provides a framework for developing, deploying, and automating a wide variety of processes. Setting data move-ments aside for the moment, here are a few examples of other management processes facilitated by SSIS solutions:■
■ file system and ftp access For data that resides in or is transported by using files, the complete set of file and file system management operations is supported in SSIS.
Whether the files exist in the file system, are accessible through the local network, or reside at remote locations that are accessible via File Transfer Protocol (FTP), SSIS can be used to automate file system operations (such as downloading files from or uploading them to remote locations and managing files in the local file system).
■
■ external processes Processes that exist outside the SQL Server environment can be invoked by using SSIS, for instance, to facilitate data processing that cannot be integrated with internal data processing tasks (perhaps for compatibility reasons) or need not be integrated with those internal tasks (perhaps because the cost of com-plete integration would outweigh the benefits). An example is an on-demand service that extracts data from an external data store and places it in files that can then be processed with additional programmatic logic implemented in an SSIS package. If such an external process is invoked by using the SSIS package, the resulting solution can be deployed, maintained, and used as a whole, even though its individual parts essentially remain heterogeneous.