Module 05 : Lab
Exercise 1: Using a Datetime Column to Incrementally Extract Data
Task 1: Prepare the Lab Environment
1. Start SQL Server Management Studio and connect to the (local) instance of the SQL Server database engine by using Windows authentication.
1. Restore files all files in the « LABS-Atelier SID\Lab05\BackupFiles » folder. Indication : Refer to «Préparation Environnement Labxx » file in the « LABS-Atelier SID » folder for help.
Et je fais les mêmes démarches pour les autres tables .
1. Run the « SetupAfterRestore.sql » file.
Task 2: View Extraction Data
2. In Object Explorer, expand Databases, Staging, and Tables.
1. Right-click dbo.ExtractLog and click Select Top 1000 Rows.
1. View the data in the ExtractLog table, noting the values in the LastExtract column, which indicate the date and time of the last extract operations for the InternetSales and ResellerSales databases. This is initially set to January 1st 1900.
1. In Object Explorer, under Databases, expand InternetSales and Tables.
1. Right-click dbo.SalesOrderHeader and click Select Top 1000 Rows. Then note that the LastModified column indicates the date and time that the sales record was last modified.
Task 3: Examine an Incremental Data Extraction
1. Start Visual Studio and open the AdventureWorksETL.sln solution in the « LABS-Atelier SID\Lab05\Ex1 » folder.
1. In Solution Explorer, under SSIS Packages, double-click Extract Reseller Data.dtsx.
3. On the SSIS menu, click Variables and then in the Variables pane, note that the following variables have been defined with a data type of DateTime:
• CurrentTime
• ResellerSalesLastExtract
1. On the Control Flow tab of the design surface, double-click the Get Current Time task, and note that it uses the GETDATE() function to assign the current data and time to the CurrentTime variable. Then click Cancel to close the Expression Builder dialog box.
5. Double-click the Get Last Extract Time task to open the Execute SQL Task Editor dialog box, and note the following configuration settings. Then click Cancel:
- On the General tab, the task is configured to return a single row from the Staging database by executing the following Transact-SQL statement:
SELECT MAX(LastExtract) LastExtract
FROM ExtractLog
WHERE DataSource = 'ResellerSales'
Publicité
• On the Result Set tab, the LastExtract column in the result returned by the query is assigned to the User::ResellerSalesLastExtract variable.
- 6. Double-click the Extract Reseller Sales task to view its data flow, and then on the Data Flow tab, double-click the Reseller Sales source to view its settings.
- 7. On the OLE DB Source Editor dialog box, note that the SQL command text used to retrieve the reseller sales data includes the following parameterized WHERE clause :
WHERE LastModified > ?
AND LastModified <= ?
1. Click the Parameters button, and note that the parameters in the WHERE clause are mapped to the User::ResellerSalesLastExtract and User::CurrentTime variables. Then click Cancel to close the Set Query Parameters dialog box, and click Cancel again to close the OLE DB Source Editor dialog box.
9. Click the Control Flow tab, and then double-click the Update Last Extract Time task and note the following configuration settings. Then click Cancel:
• On the General tab, the task is configured to update the Staging database by executing the following parameterized Transact-SQL statement:
UPDATE ExtractLog
SET LastExtract = ?
WHERE DataSource = 'ResellerSales'
- • On the Parameter Mapping tab, the parameter in the query is mapped to the User::CurrentTime variable.
- 10. On the Debug menu, click Start Debugging, and observe the control flow as the package runs. When package execution is complete, on the Debug menu, click Stop Debugging.
Pour réaliser cette étape, on doit créer un checkpoint et changer le path de orphaned sales.
- 11. Maximize SQL Server Management Studio and in Object Explorer, in the Tables folder for the Staging database, right-click dbo.ExtractLog and click Select Top 1000 Rows. Note that the last extract time for the ResellerSales data source has been updated. Then minimize SQL Server Management Studio.
Task 4: Define Variables for Extraction Times
1. In Visual Studio, in Solution Explorer, double-click Extract Internet Sales Data.dtsx.
2. • Name: CurrentTime • Data Type: DateTime
3. Click the Add Variable button again, and add a second variable with the following settings:
• Name: InternetSalesLastExtract • Data Type: DateTime
4. In the SSIS Toolbox, drag an Expression Task inside the Extract Customer Sales Data sequence container on the control surface. Arrange the tasks in this container so that the new expression task is above the Extract Customers task. Then right-click the expression task, click Rename, and change the name to Get Current Time.
5. Double-click the Get Current Time task, and in the Expression Builder dialog box, in the Expression box, specify the following expression and then click OK:
@[User::CurrentTime] = GETDATE()
1. In the SSIS Toolbox, drag an Execute SQL task to the control flow surface and drop it inside the Extract Customer Sales Data sequence container, immediately below the Get Current Time task. Then right-click the Execute SQL task, click Rename, and change the name to Get Last Extract Time.
7. Double-click the Get Last Extract Time task and in the Execute SQL Task Editor dialog box, configure the following settings. Then click OK:
• On the General tab, in the Connection drop-down list, select localhost.Staging.
Publicité
• On the General tab, in the SQLStatement box, click the ellipsis (…) button and then in the Enter SQL Query dialog box, enter the following Transact-SQL query and click OK:
- On the General tab, in the ResultSet drop-down list, select Single row.
- • On the Result Set tab, click Add, and then in the Result Name column, change NewResultName to LastExtract, and in the Variable Name drop-down list, select User::InternetSalesLastExtract.
1. On the control flow surface, click the Get Current Time task and drag its green precedence constraint to the Get Last Extract Time task. Then click the Get Last Extract Time task and drag its green precedence constraint to the Extract Customers task.
Task 5: Modify a Data Source to Filter Data
1. On the control flow surface, double-click the Extract Internet Sales task to display its data flow.
2. On the data flow surface, double-click the Internet Sales source, and in the OLE DB Source Editor dialog box, review the existing SQL command text used to extract sales data. Then add the following parameterized WHERE clause to the SQL Command text:
WHERE LastModified > ?
AND LastModified <= ?
3. Click the Parameters button, and in the Set Query Parameters dialog box, specify the following parameter mappings with a Param Direction of Input, and click OK:
• Parameter0: User::InternetSalesLastExtract
• Parameter1: User:CurrentTime
Task 6: Add a Task to Update the Extraction Log
1. Click the Control Flow tab, and then in the SSIS Toolbox, drag an Execute SQL Task to the Extract Customer Sales Data sequence under the Extract Internet Sales task on the control flow surface.
1. Right-click Execute SQL Task and click Rename. Then change the name to Update Last Extract Time.
3. Double-click Update Last Extract Time and configure the following settings. Then click OK:
• On the General tab, in the Connection drop-down list, select localhost.Staging.
• On the General tab, in the SQLStatement box, click the ellipsis (…) button and then in the Enter SQL Query dialog box, enter the following Transact-SQL query and click OK:
- • On the Parameter Mapping tab, click Add and create the following parameter mapping:
- o Variable Name: User::CurrentTime
- o Direction: Input
- o Data Type: DATE
- o Parameter Name: 0
- o Parameter Size: -1
- 4. On the control flow surface, click the Extract Internet Sales task and then drag its green precedence constraint to the Update Last Extract Time task.
Task 7: Test the Package
1. Click the Data Flow tab and view the Extract Internet Sales data flow.
1. On the Debug menu, click Start Debugging and observe the package as it executes, noting the number of rows transferred.
1. Maximize SQL Server Management Studio, and in Object Explorer, in the Staging database, rightclick the dbo.ExtractLog table and click Select Top 1000 Rows.
Publicité
1. View the data in the ExtractLog table, noting the value in the LastExtract column for the InternetSales database has been updated to the date and time when you ran the package.
1. Right-click the dbo.InternetSales table and click Select Top 1000 Rows. The sales records in this table were extracted from the InternetSales database, where the SalesOrderHeader table had a LastModified column value between the previous LastExtract value, and the date and time when the package was executed.
1. Minimize SQL Server Management Studio.
8.In Visual Studio, with the Extract Internet Sales data flow displayed in the designer, on the Debug menu, click Start Debugging to execute the package again, noting that no rows are transferred during this execution
9. When execution is complete, on the Debug menu, click Stop Debugging. Then close Visual Studio. Results: After this exercise, you should have an SSIS package that uses the high water mark technique to extract only records that have been modified since the previous extraction.
Exercise 2: Using Change Data Capture
Task 1: Enable Change Data Capture
1. Maximize SQL Server Management Studio, and open the Enable CDC.sql file in the « LABS-Atelier SID\Lab05\Ex2 » folder.
1. Examine the Transact-SQL code in this script, noting that it enables CDC in the InternetSales database, and for the Customers table. Then click Execute to run the script. Two jobs should be started.
3. Open the Test CDC.sql file in the « LABS-Atelier SID\Lab05\Ex2» folder, and examine the query, noting that it contains statements to perform the following tasks:
• Retrieve data changes between 1/1/1900 and the current date by using a CDC function.
• Modify the data in the Customers table.
• Retrieve data changes between 1/1/1900 and the current date again.
1. Select the code under the comment Select all changed customer records between 1/1/1900 and today and click Execute. Note that no records are returned because there have been no changes in the database since Change Data Capture was enabled.
1. Select the two UPDATE statements under the comment Make a change to all customers (to create CDC records) and click Execute. This statement modifies the data in the Customers table by reversing the FirstName value and then reversing it back to its original value.
1. Select the code under the comment Now see the net changes and click Execute. Note that the query returns all records in the Customers table, because they have all been changed within the specified time period.
Task 2: Create a Stored Procedure to Retrieve Modified Rows
1. In SQL Server Management Studio, open the Create SP.sql file in the « LABS-Atelier SID\Lab05\Ex2 » folder.
2. Examine the Transact-SQL code in the query file, and note that it creates a stored procedure with StartDate and EndDate parameters. The stored procedure performs the following tasks:
• Retrieves the log sequence numbers for the dates specified in the StartDate and EndDate parameters.
• If neither of the log sequence numbers is null, then at least one transaction has occurred in the database within the specified time period. The stored procedure uses a CDC function to return all records that have changed in the Customers table.
- • If no transactions have taken place in the specified time period, the stored procedure returns an empty rowset.
- 3. Click Execute to run the Transact-SQL code and create the stored procedure.
- 4. Click New Query, and type the following Transact-SQL in the new query window. Then click Execute to test the stored procedure:
USE InternetSales
GO
EXEC GetChangedCustomers '1/1/1900', '1/1/2099';
Publicité
GO
(18485 row(s) affected)
Task 3: Use the Stored Procedure in a Data Flow
1. In SQL Server Management Studio, open the Reset Staging.sql file in the « LABS-Atelier SID\Lab05\Ex2 » folder.
1. Click Execute to reset the staging database.
1. Minimize SQL Server Management Studio.
2. Start Visual Studio and open the AdventureWorksETL.sln solution in the « LABS-Atelier SID\Lab05\Ex2 » folder.
1. In Solution Explorer, under SSIS Packages, double-click Extract Internet Sales Data.dtsx.
1. On the control flow surface, double-click the Extract Customers task.
1. On the data flow surface, double-click the Customers source.
8. In the OLE DB Source Editor dialog box, make the following changes to the configuration of the Customers source. Then click OK:
• In the Data access mode drop-down list, select SQL Command.
• In the SQL command text box, type the following Transact-SQL statement:
EXEC GetChangedCustomers ?, ?
- • Click the Parameters button, and in the Set Query Parameters dialog box, create the following parameter mappings with a Param direction of Input, and then click then OK.
- o @StartDate: User::InternetSalesLastExtract
- o @EndDate: User::CurrentTime
Task 4: Test the Package
1. With the Extract Customers data flow displayed in the designer, on the Debug menu, click Start Debugging and observe the package as it executes, noting the number of rows transferred.
18485 rows
1. When execution is complete, on the Debug menu, click Stop Debugging.
3. Maximize SQL Server Management Studio, and in Object Explorer, in the Staging database, rightclick the dbo.ExtractLog table and click Select Top 1000 Rows.
4. View the data in the ExtractLog table, noting the value in the LastExtract column for the InternetSales database has been updated to the date and time when you ran the package.
1. Right-click the dbo.Customers table and click Select Top 1000 Rows. The customer records in this table were extracted from the InternetSales database, where no row has been changed between the previous LastExtract value, and the date and time when the package was executed.
1. Minimize SQL Server Management Studio.
2. In Visual Studio, with the Extract Customers data flow displayed in the designer, on the Debug menu, click Start Debugging to execute the package again, noting that no rows are transferred during this execution.
8. When execution is complete, on the Debug menu, click Stop Debugging. Then close Visual Studio.