1. In SQL Server Management Studio, open D:\10777A\Labfiles\Lab07A\Starter\Ex4\Create SP.sql. 2. Examine the Transact-SQL code in the query file, and note that it enables snapshot isolation and
creates a stored procedure with a single parameters named LastVersion. The stored procedure performs the following tasks:
• Sets the isolation level to snapshot.
• Retrieves the current change tracking version number.
• If the LastVersion parameter is -1, the stored procedure assumes that no previous versions have been retrieved, and returns all records from the Resellers table.
• If the LastVersion parameter is not -1, the stored procedure retrieves all changes between
LastVersion and the current version.
• The stored procedure updates the LastVersion parameter to the current version, so the calling application can store the last version retrieved for next time.
MCT USE ONL
Y. STUDENT USE PR
OHIBITED
Lab 7A: Extracting Modified Data L7-61
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 ResellerSales GO
DECLARE @p BigInt = -1
EXEC GetChangedResellers @p OUTPUT SELECT @p LastVersionRetrieved EXEC GetChangedResellers @p OUTPUT
X Task 3: Reset the staging database
1. In SQL Server Management Studio, open D:\10777A\Labfiles\Lab07A\Starter\Ex4\Reset Staging.sql. 2. Click Execute to reset the staging database.
3. Minimize SQL Server Management Studio.
X Task 4: Modify a data flow to use the stored procedure
1. In the D:\10777A\Labfiles\Lab07A\Starter\Ex4 folder, double-click AdventureWorksETL.sln to open the solution in SQL Server Data Tools.
2. In Solution Explorer, under SSIS Packages, double-click Extract Reseller Data.dtsx.
3. If the Variables pane is not visible, on the SSIS menu, click Variables. Then, in the Variables pane, click the Add Variable button and add a variable with the following settings:
• Name: PreviousVersion • Data Type: Decimal • Value: 0
4. In the SSIS Toolbox, drag an Execute SQL Task to the control flow surface and drop it above the
Extract Resellers task. Then right-click the expression task, click Rename, and change the name to Get Previously Extracted Version.
5. Double-click the Get Previously Extracted Version task and in the Execute SQL Task Editor dialog box, configure the following settings. Then click OK:
• On the General tab, in the ResultSet drop-down list, select Single row.
• 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.
SELECT MAX(LastVersion) LastVersion FROM ExtractLog
WHERE DataSource = 'ResellerSales'
• On the Result Set tab, click Add, and then in the Result Name column change
NewResultName to LastVersion, and in the Variable Name drop-down list, select User::PreviousVersion.
MCT USE ONL
Y. STUDENT USE PR
OHIBITED
L7-62 Module 7: Implementing an Incremental ETL Process
6. On the control flow surface, right-click the green precedence constraint between Get Last Extract
Time and Extract Resellers, and click Delete.
7. Click the Get Last Extract Time task and drag its green precedence constraint to the Get Previously
Extracted Version task. Then click the Get Previously Extracted Version task and drag its green
precedence constraint to the Extract Resellers task.
8. In the SSIS Toolbox, drag an Execute SQL Task to the control flow surface and drop it under the
Update Last Extract Time task.
9. Right-click Execute SQL Task and click Rename. Then change the name to Update Previous
Version.
10. Double-click Update Previous Version 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.
UPDATE ExtractLog SET LastVersion = ?
WHERE DataSource = 'ResellerSales'
• On the Parameter Mapping tab, click Add and create the following parameter mapping.
Variable Name Direction Data Type Parameter Name Parameter Size
User::PreviousVersion Input LARGE_INTEGER 0 -1
11. On the control flow surface, right-click the green precedence constraint between Update Last
Extract Time and Send Success Notification, and click Delete. Then click the Update Last Extract Time task and drag its green precedence constraint to the Update Previous Version task; and click
the Update Previous Version task and drag its green precedence constraint to the Send Success
Notification task.
12. On the control flow surface, double-click the Extract Resellers task. 13. On the data flow surface, double-click the Resellers source.
14. 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 GetChangedResellers ? OUTPUT
• Click the Parameters button, and in the Set Query Parameters dialog box, create the following parameter mappings and click then OK.
Parameters Variables Param direction
MCT USE ONL
Y. STUDENT USE PR
OHIBITED
Lab 7A: Extracting Modified Data L7-63
X Task 5: Test the package
1. With the Extract Resellers 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.
2. 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, right- click the dbo.ExtractLog table and click Select Top 1000 Rows.
4. View the data in the ExtractLog table, noting the value in the LastVersion column for the
ResellerSales database has been updated to the latest version retrieved from the source database.
5. Right-click the dbo.Resellers table and click Select Top 1000 Rows. The customer records in this table were extracted from the ResellerSales database, where row has been changed between the previous LastVersion value, and the current version.
6. Close SQL Server Management Studio without saving any changes.
7. In SQL Server Data Tools, with the Extract Resellers 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 SQL Server Data Tools.
MCT USE ONL
Y. STUDENT USE PR
MCT USE ONL
Y. STUDENT USE PR
OHIBITED
L7-65