{"id":2182,"date":"2014-02-19T09:56:52","date_gmt":"2014-02-19T09:56:52","guid":{"rendered":"http:\/\/bicortex.com\/?p=2182"},"modified":"2014-02-19T10:27:17","modified_gmt":"2014-02-19T10:27:17","slug":"sql-server-data-synchronization-automation-using-tablediff-utility-and-ssis-part-2","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/sql-server-data-synchronization-automation-using-tablediff-utility-and-ssis-part-2\/","title":{"rendered":"SQL Server Data Synchronization Automation Using Tablediff Utility and SSIS – Part 2"},"content":{"rendered":"

In the FIRST<\/b><\/a> post this short series I briefly outlined tablediff utility and set the stage (databases, objects, directories and batch file) for the SSIS package responsible for data synchronization workflow. In this post I will continue with the solution development and show you how to build and configure the package to take advantage of the tablediff tool and merge data across the 2 databases and their objects deployed as per PART 1 <\/b><\/a>prelude. The complete package can be downloaded from my SkyDrive folder HERE<\/b><\/a> if you wish to use it as a template – just make sure you adjust all necessary parameters e.g. database connection details etc. to correspond to your environment. I have also attached a short video footage depicting the package execution and the outcome on the tables\u2019 data synchronization and SQL files management at the end of this post.<\/p>\n

The complete package looks as per image below, with the core functionality encapsulated in the first two sections. The remaining Script Task and the second ForEach Loop Container are there to provide additional features i.e. the Script Task checking whether the file(s) have been created in the nominated directories and the second ForEach Loop Container to execute scripts generated by the tablediff utility and archive the files processed into a separate directory.<\/p>\n

\"Tablediff_complete_SSIS_package\"<\/a><\/p>\n

Let\u2019s start with creating a bunch of variables which will house the data temporarily used during package runtime or provide value-dependant execution topology. Most of the variables are pretty self-explanatory with names reflecting their function. Common_Var variable of the System.Object type will house the results of the query against the Params_details table (see previous post HERE<\/b><\/a>) whereas the Boolean File_Exists variable is populated by the Script Task and its value determines execution pathway depending on whether the file(s) have been created or not. Finally, Temp_FileName is used by the second ForEach Loop Container in iterating through SQL files created.<\/p>\n

\"Tablediff_SSIS_variables_collection\"<\/a><\/p>\n

Looking at the Connection Manager, there is one database connection pointing to the Source_DB database and 2 file connections \u2013 Archived_Files which points to the archive directory (C:\\Diffoutput\\Archived_Files) and Temp_FileName one which is using an expression for connection string as per image below.<\/p>\n

\"Tablediff_Temp_Filename_conn_expression\"<\/a><\/p>\n

Let\u2019s go through the Control Flow pane now and outline the individual containers roles. Starting with Execute SQL Task 1 transformation, it simply executes the SELECT SQL statement, returning the data as a full results set, which in turn is mapped to the Common_Var variable as per below.<\/p>\n

\"Tablediff_SSIS_Exec_SQL1\"<\/a><\/p>\n

\"Tablediff_SSIS_Exec_SQL1_Var_Assignment\"<\/a><\/p>\n

Next, the first ForEach Loop Container is configured to use the ADO object source variable Common_Var and the following variables mappings.<\/p>\n

\"Tablediff_SSIS_ForEach_Loop1\"<\/a><\/p>\n

\"Tablediff_SSIS_ForEach_Loop1_Var_Mapping\"<\/a><\/p>\n

Note that the column order used in the SELECT SQL statement from the first transformation is corresponding to the variables assignment in the first ForEach Loop Container, otherwise the package will fail.<\/p>\n

Execute Process Task simply runs the batch file saved in the nominated directory, passing the variables as arguments in a form of an expression as per below. Also, note that the order in which those appear in the expression corresponds to the SELECT SQL statement attributes order and variable mapping in the ForEach Loop Container order.<\/p>\n

\"Tablediff_exec_process_process_property\"<\/a><\/p>\n

\"Tablediff_exec_process_expression_property\"<\/a><\/p>\n

Notice that the FailTaskIfReturnCodeIsNotSuccessValue property is set to False. This is due to the fact that if no changes are detected, the tablediff will return SucessValue of 1 which effectively fails package execution. As we don\u2019t want to treat this as an error or exception FailTaskIfReturnCodeIsNotSuccessValue property needs to be changed from its default setting to False value.<\/p>\n

Following on, we have a Script Component with a very simply VB.NET code snippet checking for SQL files existence. I know in the first post I said that no code will be required but this section is not compulsory, uses default SSIS functionality and is only there as an enhancement so you can remove it if you wish. The File_Exists variable is also listed in the ReadOnlyVariables property.<\/p>\n

\"Tablediff_SSIS_script_task_config\"<\/a><\/p>\n

\r\n#Region "Imports"\r\nImports System\r\nImports System.Data\r\nImports System.Math\r\nImports Microsoft.SqlServer.Dts.Runtime\r\nImports System.IO\r\n#End Region\r\n\r\n<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute()> _\r\n<System.CLSCompliantAttribute(False)> _\r\nPartial Public Class ScriptMain\r\n    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase\r\n\r\n    Public Sub Main()\r\n        Dim directory As DirectoryInfo = New DirectoryInfo("c:\\DiffOutput")\r\n        Dim file As FileInfo() = directory.GetFiles("*.sql")\r\n\r\n        If file.Length > 0 Then\r\n            Dts.Variables("User::File_Exists").Value = True\r\n        Else\r\n            Dts.Variables("User::File_Exists").Value = False\r\n        End If\r\n\r\n        Dts.TaskResult = ScriptResults.Success\r\n        Dts.TaskResult = ScriptResults.Success\r\n    End Sub\r\n\r\n#Region "ScriptResults declaration"\r\n    Enum ScriptResults\r\n        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success\r\n        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure\r\n    End Enum\r\n#End Region\r\nEnd Class\r\n<\/pre>\n
This code also populates File_Exists variable which is being used in the Precedence Constraints joining the subsequent transformations. It contains the conditional expression which when evaluated, returns a Boolean value altering the package execution flow i.e. pointing it in either of the 2 directions \u2013 Execute SQL Task 1 or ForEach Loop Container 2. Below is the Precedence Constraint editor view for executing ForEach Loop Container 2 section.<\/p>\n
\"Tablediff_SSIS_precedence_constraint_formula\"<\/a><\/p>\n
In the last section, ForEach Loop Container 2 iterates through SQL files created using the following enumerator configuration and variable mapping.<\/p>\n
\"Tablediff_ForEach_Loop2_Var_Mapping\"<\/a><\/p>\n
Inside the ForEach Loop Container 2, Execute SQL Task 3 transformation runs the SQL statements saved in the files, with SQL Source Type property configured as a File Connection and Temp_FileName connection selected as a value for File Connection property. File System Task simply archives the processed files into the archive sub-directory as the last step of this package\u2019s execution.<\/p>\n
\"Tablediff_SSIS_ForEach_Loop2\"<\/a><\/p>\n
\"Tablediff_SSIS_file_sys_task1\"<\/a><\/p>\n
The complete package can be downloaded from my SkyDrive folder HERE<\/b><\/a> so you can use it as a template – just make sure you adjust all necessary parameters e.g. database connection details etc. to correspond to your environment. I have also attached a short video footage depicting the package execution and the outcome on the tables\u2019 data synchronization and SQL files management.<\/p>\n