Using the change data capture synchronization in SQL Server Integration Services
Change data capture transformations are designed to be used with Microsoft SQL Server Integration Services.
To use the change data capture synchronization:
1. Create a new Data Dictionary, different than the one used for DataSync, to use for the transformation data tables.
Use the CONNX Data Dictionary tool. Click Cancel in the Open dialog box to get to a new empty Data Dictionary.
2. Press the Import button and select Change Data Capture Target in the Import Type dropdown list.
3. Put the name of the server where DataSync is running in the Server field.
4. Enter the user name and password that were used to connect to the DataSync Data Dictionary in the UserName and Password fields.
5. In the Import Table Selection screen, press the Add All button to get all the change data capture tables.
These will be used later by the SQL Server Integration Services Work packages to get the rows that have been inserted, updated or deleted.
6. Press OK, save the Data Dictionary, and close the Data Dictionary Manager tool.
The new CDD can be in the same folder as the DataSync Data Dictionary, but it needs to have a different name.
7. Start your SQL Server Business Intelligence Development Studio.
8. Click File --> New --> Project.
9. Expand the Business Intelligence template folder and select Integration Services Project. Enter a name and press the OK button.
This opens an empty Package.dtsx environment, with Control Flow as the selected tab.
10. Right click in the Connection Managers tab in the lower part of the Control Flow tab and select New Connection... from the pop up menu.
11. Select CONNX from the list of Connection manager Types and press the Add... button.
12. Enter the name, location, user name, and password for the CONNX Data Dictionary that contains the transform tables. Press the OK button and the environment will connect to the data dictionary to verify the connection information.
13. In the SSIS toolbox, drag the CONNX Sync Task to the design space. This will run the Savepoint Sync in the DataSync tool from the SSIS package. Rename it to " CONNX Sync Task - Savepoint Customers".
14. Double click on the CONNX Sync Task to configure it.
If there is only one CONNX Connection manager, it will connect to that and list the available Change Data Capture Transforms.
15. Select the transform you want to sync and then select the type of Sync to run on the right.
The descriptions are:
Savepoint sync - A Savepoint sync task will tell the DataSync transformation server to find all the changes that have occurred since the last time a Baseline sync was run. This can be run both from DataSync and this CONNX Sync task.
Baseline sync - A Baseline sync task will tell the DataSync transformation server to read and save the data as it is. This updates the data, so all future Savepoint syncs will show only changes from this update. This can be run from both DataSync, and this CONNX Sync task.
Move Baseline to Savepoint sync - This tells the DataSync transformation server to make the last Savepoint sync the new change baseline and is the perfect way to reset the starting point for tracking changes after the previous changes have been migrated to the targets. It doesn't actually run a Baseline sync, it simply sets the last Savepoint sync to be the new baseline.
16. Press OK to close the window and save the changes.
Note:
The SQL string at the bottom of the screen is created by selecting the transform and type of sync and is displayed only so the admin knows what SQL is actually being executed. No user editing of this line is necessary.
17. From the SSIS toolbox, drag a Data Flow Task to the designer and double click on it to bring up the Data Flow designer.
The SSIS toolbox now displays only tools that work in a Data Flow Task.
18. Drag a CONNX SSIS year Source object to the design pane.
In the Business Development Studios for SQL Server 2012 and 2014, the toolbox will automatically update with the CONNX SSIS components. In Business Development Studio for SQL Server 2005 and 2008 you will need to add them to the toolbox. If there is no CONNX SSIS year Source object in the toolbox, right click on the toolbox and select Choose Items... from the popup menu. The list of available tools will take several seconds to appear. When it does, choose SSIS Data Flow Items and put a check in the box next to CONNX SSIS year Source.
19. Double click on the CONNX SSIS year Source box in the design window to configure the source data.
If there is only one CONNX Connection manager, the environment will automatically connect to it and display the change data capture tables.
The list of tables will have the name of the transform first, then be appended with what data is in the table. Choose table to get all new rows that have been inserted into that table.
20. Press the OK button to save the new configuration and return to the Data Flow designer.
Another option to specifying a table is to use a select SQL Statement using the changed tables. You can use this option for customized data retrieval, however likely all the change data is needed, thereore selecting a table will suffice.
The Data Flow is now ready to move the change data to a target.
21. Go to the toolbox in the Data Flow Destinations section and drag SQL Server Destination to the design pane.
22. Drag the green arrow from the CONNX SSIS 2012 Source object to the SQL Server Destination object.
The designer will create a green line between the two objects.
23. Right click on the SQL Server Destination object and select Edit.
This will open the SQL Destination Editor on the Connection manager screen.
24. Select the New... button next to the Connection Manager dropdown list.
That will bring up the Connection Configure screen. If you have a SQL Server connection, it will be displayed in the list. If not, select the New button and input the connection information to the SQL Database you wish to be the target.
25. Select OK to finish.
The specified connection will now be in the Connection Manager dropdown menu.
26. Select a table to move the data to from the Use a table or view dropdown menu.
If the target is going to be a new table, press the New... button and specify the name in the create table statement. When you click OK, the new table will be selected in the dropdown menu.
27. Select Mappings in the list on the left.
The default mappings will be displayed.
28. If necessary, change the mappings press OK to return to the Data Flow designer.
The package is now ready to move change data. You can run the package by pressing F5. Green indicates a successful run, red indicates an error. If there has not been any changes on the source data, the sync will be green for success, however no entries will be moved.