OverviewSyteview DataLinks are small programs that you can use in conjunction with Progress table triggers to replicate data in real-time from your Progress database to your Microsoft SQL Server. Each DataLink handles the replication of data from one table. Data Links are normally used to keep the MS-SQL Server database synchronized with the Progress database in between the times that you run the DataGrabber programs.Syteview includes DataLinks for many of the more time sensitive tables in your Progress Syteline® database along with the tools necessary for you to create your own Data Links. The following table lists the DataLinks that come on your Syteview Installation CD, and their locations in the Syteview installation directory on your MS-SQL Server:
The DataLink programs work by reading small text files that represent records that have been added, deleted or modified in the Progress database. When a record is added (or deleted or modified) a trigger in the affected table executes. This trigger uses the Progress EXPORT command to write out each of the fields of the record to the text file. Each of these text files is written to a sub-directory that is unique for each table. The sub-directories which these text files are written to are watched by the DataLink programs. Each DataLink program watches its own, unique sub-directory. When a DataLink program notices that one of these text files has been created it reads in the text file and uses the data it contains to create a record compatible with the Microsoft SQL Server. This SQL Server compatible record is then added to or deleted from the appropriate table in the SQL Server’s Syteline database. In cases where a record is modified in the Progress database, the matching record is first deleted from the SQL Server’s database and then added back in with the new values. The sub-directory that each DataLink watches is passed to the DataLink on the command line when the DataLink starts up. This sub-directory is called the Progress directory and you pass it on the command line with the /P switch. Example: LinkCOs.exe /P:D:\DataLinks\COs\ /L:D:\DataLinks\COs\LogFiles\ After each DataLink reads in and processes a text file it writes out another text file for logging purposes which it places in a sub-directory called the Log directory. The Log directory that will be used is passed to the Data Link programs on the command line when they start up with the /L switch. Note: Each DataLink must use a unique sub-directory for both its Progress and its Log directory. Each DataLink includes the Microsoft Visual Basic source code for the DataLink which allows you to modify the DataLink program if necessary. This allows you to change your DataLinks when the structure of one of your Progress tables changes or to use the DataLinks to move data to other programs or databases. One of the major benefits of using text files to keep your Progress and SQL Server databases synchronized is that your Progress database can be kept isolated from the internet (and your intranet). When using DataLinks You do not need to make an ODBC connection to your Progress database. Another benefit of using text files to keep your databases synchronized is that your Log directories keep an excellent record of all the transactions that flow through your DataLinks. These text files can even be used to keep more than one SQL Server synchronized with your Progress database and since these files can easily be transported, these SQL Servers can be widely distributed and still be kept in-sync using any number of methods including, FTP, email transfers, zip drives, tapes, etc. Creating Progress TriggersBefore you can use a DataLink you will have to create a trigger for the table in the Progress database that it will be exporting data from. Do not create a table trigger on your production Progress database server until you thoroughly understand this process. Especially the requirement for each of your users’ workstations to have a drive letter mapped to the triggers file output directory.To create one of these triggers open the Progress Data Dictionary program and connect to your Progress database. After connecting to your Progress database, highlight the table you want to add a trigger to and click the Table Properties button. For this example we will be using the co table.  When the Table Properties dialog box appears click the Triggers button.  Note: Syteview DataLink programs work with Insert, Delete and Write triggers, but due to the way Syteline® inserts records you will only need to use Delete and Write triggers. When Syteline® wants to create a new record it will first insert a blank record and then update (edit) the blank record. The Write trigger will fire when the values for this new record have been filled in and the record is saved. If you attach the trigger code to the Insert trigger you will get nothing but the blank record. The Write trigger will generate all the information you need to know about the new record. When the Table Triggers dialog box appears select either the Delete or the Write Trigger from the Event pull down list. In this example we have selected the Delete trigger.  After selecting the Delete event you need to copy the Progress code that will execute when the trigger fires into the Delete trigger. This code is in the CO-DELETE.p file which is located in the \ProgressTriggers directory in the Syteview SQL installation directory. To copy this code into the delete trigger click the Files button. When the Find Trigger Procedure dialog box appears, navigate to the \ProgressTriggers directory in the Syteview installation directory, highlight the CO-DELETE.p file and then click the Open button.  This will insert the trigger code into the code window of the Table Triggers dialog box.  You may have to edit the code, changing the value of the fout variable in the trigger code to reflect the location that you want the trigger to write the outputted text file to.  The output location of the trigger code that Syteview provides defaults to the T:\XXXs directory where XXX is the name of the table that you are attaching the trigger code to. For example: the CO table Delete and Write triggers output their text files to T:\COs\ Code Example: fout = "T:\COs\delete.co." + string(YEAR(TODAY)) + m + d + "." + t + "." + e + "." + string(RANDOM(1000,9999)) + "." + STRING(RANDOM(1000,9999)) + ".d". Note: the trigger file’s output path must be available to all workstations that will be causing these triggers to fire, so the T: drive may not be the best drive letter to use in your situation. Select a drive letter that can be mapped identically on each of your users’ workstations. For example, share the D:\Syteview\Triggers sub-directory on the computer running your Progress database server as SyteTriggers and then map the drive letter that you will use on each workstation to the SyteTrigger share Click the Save button on the Table Triggers dialog box to save your work, followed by the OK button to close the window. Repeat the process for the CO table’s Write trigger, this time selecting the CO-WRITE.p file from the \ProgressTriggers directory. Now that you have created the Delete and Write triggers for the CO table, create, update or delete a record in your Progress CO table and verify that the trigger created a text file in the directory specified by the fout variable. This text file will be read in by the LinkCOs DataLink program which will then change a few things to make the Progress record’s structure compatible with the record structure of the CO table in the Syteview database on SQL Server. After the DataLink program fixes up the record’s structure it will insert the record into the CO table in the Syteview database (or delete the record if appropriate) and then write the fixed up file to a log file in the LOG directory.. Repeat the above procedure creating Delete and Write triggers in the Progress tables of any other DataLinks you want to use. Note: Once the trigger writes the text file your Progress database server is done. All other processing is handled by the DataLink programs and the SQL Server(s). This places an almost imperceptible load on your Progress database server. Note: Once the text file is written it can be read by more than one DataLink program. This gives you the ability to keep geographically distributed SQL Servers synchronized. Syteview Version 3 will give you the option of automating this process using standard internet protocols. Running DataLink ProgramsNow that you have created Delete and Write triggers in the Progress tables of each of the DataLink programs you will be using you are ready to start running them. There is a batch file (Startup.DataLinks.bat) in the \DataLinks directory in the Syteview installation directory that can be used to startup all of your DataLinks.Edit this batch file changing the paths to the DataLinks to the actual path on your computer. You may also have to edit the locations of the Progress directory (the directory the DataLink watches for the text files that the Progress triggers create) and the locations of for the Log files that the DataLinks create. Once you have edited this batch file to reflect the actual paths used by your system you can then startup all of your DataLinks with a double-click of the mouse. There is also a batch file in the home directory of each of the DataLink’s that can be used to run a single DataLink. As with the batch file that starts up all of the DataLink programs at once, the this batch file will have to edited to match the actual locations used by your system. After you have edited one or all of the batch files, start one of the DataLink programs running. You should see something similar to the following screen.  This is the user interface of the CO DataLink. When it starts up it will be watching the T:\COs directory for trigger generated text files. When one appears it will be processed and the record that it represents will be put in or taken out of the CO table in the Syteview database on the SQL Server. If the text file represents a record that was inserted into the CO table in the Progress database then a matching record will be inserted into the CO table in the Syteview database. Similarly, if the text file represents a record that was deleted from the CO table in the Progress database then the matching record will be deleted from the CO table in the Syteview database. Record updates are handled a little differently. If the text file represents a record that was updated in the CO table in the Progress database then the matching record will first be deleted from the CO table in the Syteview database and then a new record that matches the updated Progress record will be inserted in the CO table in the Syteview database. You can start and stop the DataLink from watching for trigger text files by clicking the button in the top left corner. When the button label says ‘Stop Polling’ then the DataLink is currently watching (polling) the Progress directory (T:\Cos in this case) for trigger text files to appear and clicking it will stop the DataLink from polling. When the DataLink stops polling the button label will change to ‘Start Polling’. Clicking the button again will start the DataLink polling again and change the button label back to 'Stop Polling'. When the DataLink has been stopped the Close button becomes enabled and you are can close the program by clicking it. The Logging To and Watching text boxes tell which directories the DataLink is currently watching and which directory it is placing its log files into. You can change these directories by editing the two text boxes. The changes you make will not take effect until you Stop and then Start the DataLink polling again. The DataLink polls (watches) the Progress directory (T:\COs in this case) at a regular frequency. This frequency is set by the value in the Polling Interval text box. This value is measured in seconds and any changes you make will not take effect until you Stop and then Start the DataLink polling again. The three other text boxes on the DataLink screen, Inserted, Updated and Deleted keep track of the number records that the DataLink has Inserted, Updated and Deleted. They are only there for informational purposes. Modifying DataLink ProgramsDataLinks can also be run without this user interface if required. This allows you to run them, either from the Task Scheduler or as a Windows Service, without requiring a user to login and start them up. You will need to edit the Visual Basic code of the DataLink if you want to run it without the user interface. To do this open the Visual Basic project (LinkCOs.vbp in this case) located in the DataLink’s home directory and edit the code located at the end of the Main() subroutine in Module1.'NOTE: ' If you are not running this program from a Visual Basic Form ' you will need to uncomment the following Do/Loop and the call ' to EndProgram in order to execute the DTS Package. ' ' If you are running this program from a Visual Basic Form then a ' similar Do/Loop will be connected to the Form's Timer control and ' the EndProgram subroutine will be called when the Form unloads. ' 'Do While LinkCOs_ProcessFile(ProgressDirectory, LogDirectory) ' ExecutePackage 'execute the DTS package 'Loop ' 'EndProgram Uncomment the Do/Loop and the line that calls the EndProgram subroutine. This will cause the DataLink to enter the Do/Loop, process any trigger text files that it finds until they have all been processed at which time it will exit the Do/Loop and call the EndProgram subroutine which closes out all data connections and ends the program. Next you will need to edit the project properties changing the Startup Object from Form1 to Sub Main.  This will cause the DataLink program to call the Main() subroutine when it starts running instead of loading the user interface (Form1). The Main() subroutine will then process any trigger text files it finds (until the directory it is watching is empty) and then it will close down the DataLink program. Now after compiling the DataLink program you can place it on the Task Scheduler and have it process any trigger text files it finds on the schedule you specify with the Task Scheduler without requiring a user to login and run the program. When running the DataLink from the Task Scheduler you can either pass the Progress directory (the directory being polled) and the Log directory to the DataLink program on the command line, or further edit the code to hard code these directories into the program. The polling frequency will not come into effect with this edited program. The program will poll the Progress directory once, processing all the trigger text files it finds there, and then close itself down. You can also remove the Form1 form from the DataLink project if you don’t plan on using the user interface in the future. Syteview provides you with the Visual Basic source code for fifteen precompiled DataLink programs. The source code for additional DataLink programs can easily be created for any other tables that you want to keep synchronized between your Progress database and the Syteview database in the SQL Server. Creating Additional DataLink ProgramsMicrosoft SQL Server uses a Windows NT resource called Data Transformation Services (DTS) to work with data services and structures. Using the Data Transformation Services you can create DTS Packages that will package together all the steps required to import data from your Progress database into the Microsoft SQL Server Syteview database in real-time. SQL Server provides you with an easy to use GUI program that helps you design these DTS Packages and then Syteview provides you with the means of creating Visual Basic programs called DataLinks from these DTS Packages.Before you can create a DTS Package that imports records from your Progress database in real-time you will have to edit a record in the Progress table that you want to create a DataLink for. This will cause the write trigger you created above to fire and generate the text file you will use in creating the DTS Package. For example, if the trigger is configured to output these text files to T:\COs you will find a file there named something like this: T:\COs\update.co.20020130.30859.000000133782.8365.8736.d Now, to create a DTS Package that imports records from your Progress Database in real-time open the SQL Server Enterprise Manager program, expand the SQL Server Group, expand your Server and then right click Data Transformation Services. Select All Tasks and then select Import Data. This will bring up the following screen.  Click the Next button to proceed. On this screen select the Text File Data Source which will connect to (read) the text files generated by your Progress database triggers.  Click the File name field’s Browse button and navigate to the \COs directory on the T: drive. Once there select the text file you created with the database trigger. Note: In our case we are creating a DataLink program to bring CO records into the SQL Server's Syteview database in real-time and the database trigger is creating the text files in the T:\COs sub-directory. Note: Make sure to change Files of type to All Files (*.*) since the triggers generate .d files.  Select the text file that you created above (update.co.20020404.26280.000000819178.6523.8783.d) and then click the Open button which will bring up the following screen.  On this screen click the Next button to continue. On the next screen change the selection from Fixed field to Delimited.  Click the Next button to continue. On this screen enter a single space in the Other field for the column delimiter type and then click the Next button.  On the next screen:
 On the next screen make sure the Destination Table shows the name of the table that you will be importing records into. Since we will be importing CO records the Destination Table will be [Syteview].[dbo].[co].  After setting the Destination Table click the Transform button to bring up the Column Mappings and Transformations screen.  On the Column Mappings tab select Append rows to destination table and then switch to the Transformation tab. On the Transformations tab select Transform information as it is copied to the destination and then click the OK button to return to the previous screen (Select Source Tables and Views).  When the Select Source Tables and Views screen displays click the Next button to continue. On this next screen uncheck the Run Immediately checkbox and check the Save DTS Package checkbox and then click the Next button.  Give the DTS Package a name, in this case LinkCOs. Make sure Use Windows NT authentication is selected and then click the Next button to proceed.  The next screen just tells you that you have successfully created a DTS Package that will import the selected table from the Progress database into the Microsoft SQL Server.  Congratulations. Click the Finish button. You just created a DTS Package to import records into the SQL Server Syteview CO table from a text file that was generated by a database trigger in the Progress database. Unfortunately, this DTS Package will not work for several reasons. Reason Number One is that the database trigger generates the text files by using the Progress Export command and this command outputs NULL values as question marks, ‘?’, which totally confuses the SQL Server. Luckily the Syteview DTS2VBCode_Link.exe program which translates the DTS Package you just created into Visual Basic source code will fix this problem for you. Reason Number Two that the DTS Package you just created won’t work is that the Progress Export command does not write out record fields in the same order that the Progress ODBC connection generates them, and the Progress ODBC connection is used to create the related table in the SQL Server Syteview database. Luckily, once again, the Syteview DTS2VBCode_Link.exe program will fix this problem for you. Reason Number Three that the DTS Package you just created won’t work is that the Progress Export command writes out fields that have extents as multiple fields (one per extent), while the Progress ODBC connection creates fields with extents as one field with all of the extents contained in it in one semi-colon delimited string. Luckily, once again, the Syteview DTS2VBCode_Link.exe program will fix this problem for you. Unluckily, because of the above three reasons, you can’t use the DTS Package you just created to directly import records. You have to use the DTS2VBCode_Link.exe program (in the \DataLinks sub-directory in the Syteview installation directory on your MS-SQL Server) to read the DTS Package and create a Visual Basic program that can make the necessary changes to the text file generated by the Progress database trigger and import the record for you. To create this Visual Basic program run the DTS2VBCode_Link.exe program and enter the name of the DTS Package you just created in the first screen that appears and then click the OK button.  This will bring up the following screen telling you that the Visual Basic program has been created:  Now, find the new program that was just created (in this case it’s D:\Syteview\DTS-to-VBCode_Link\LinkCOs.txt) and move it into a sub-directory of it’s own. Each of the DataLink programs we’ve included with Syteview are in sub-directories of their own in the \DataLinks directory. Now, using Windows Explorer highlight the LinkCOs.txt file, right click it and select Open With. When the Open With window pops up select VB 6 and click OK. Visual Basic will open with the LinkCOs.txt file in its code editor. Pull down the Project Menu and select References. In the References window checkmark the Microsoft DTSPackage Object Library and the Microsoft ActiveX Data Objects 2.1 Library and then click OK to close the window.  Next, pull down the File menu and select Save Project As. Give the project a name similar to the DTS Package name. For example: LinkCO.vbp You can now compile this code into an executable and run it. There is some code at the end of the Main() subroutine that you will want to take note of. Specifically: ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 'NOTE: ' If you are not running this program from a Visual Basic Form ' you will need to uncomment the following Do/Loop and the call ' to EndProgram in order to execute the DTS Package. ' ' If you are running this program from a Visual Basic Form then ' the Do/Loop will be connected to the Form's Timer control and ' the call to EndProgram will be called when the Form unloads. ' 'Do While LinkCOs_ProcessFile(ProgressDirectory, LogDirectory) 'process the .d file exported by Progress ' ExecutePackage 'execute the DTS package 'Loop ' 'EndProgram ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' We prefer running the LinkXXXs programs from a form-enabled Visul Basic program and have included Visual Basic forms with each of the DataLink programs we send out with Syteview. These forms display the status of the Data Link program and allow you to change the sub-directory used to watch for text files, change the sub-directory used to copy the log files into and they allow you to change the polling interval used when watching for the generated text files. The forms also keep track of the number of records processed and allow you to pause and restart the polling. If you choose to use the forms version of the DataLinks you will have to add the form (Form1.frm) to any Visual Basic projects that you create and then change the projects’ Startup Objects to Form1. To do this pull down the Project menu and select the project’s Properties. On the General tab change the Startup Object to Form1.  If you choose not to use forms with your Data Link projects then you will need to uncomment the code at the bottom of the Main() subroutine. This will cause the program to look in the subdirectory it monitors for text files one time, process any files it finds there and then exit. Normally to use DataLinks in this manner you would place the DataLinks on the Windows Task Scheduler and have the DataLinks called at specific times by the Task Scheduler. Whichever method you chose (forms or Task Scheduler), save the project , compile it and then close the Visual Basic program. To run the program you just created you should create a startup batch file or a Windows Shortcut. Use one of the batch files or shortcuts from one of the DataLinks that came with Syteview as a starting point and change the DataLink’s name and path to match the DataLink that you just created. Example of a startup batch file: D: CD \Syteview\DataLinks\COsLinkCOs /P:D:\Syteline\Symwork\COs\ /L:D:\Mssql7\Syteview\DataLinks\COs\LogFiles\ Example of a Windows Shortcut:  Whether you run the program with the form or without is entirely up to you. Either way, you will need to pass the paths to the subdirectory that the DataLink is watching for the trigger generated text files, called the ProgressDirectory, and the sub-directory you are copying the log files into, called the LogDirectory, on the command line when you startup the DataLink program. The ProgressDirectory path is passed on the command line with the /P: switch. The LogDirectory path is passed on the command line with the /L: switch. Example: LinkCOs.exe /P:d:\Syteline\Symwork\Cos /L:d:\Syteview\DataLinks\COs\LogFiles When a Data Link processes one of the text files that define a record inserted, deleted or updated it will create a text file representation of the record in its LogFile directory. These log files have date and time strings encoded in their file names. For example: insert.coitem.20011125.74193.000000106813.6259.7956.d is a coitem record that was inserted 11-25-2001 (20011125), 74193 seconds after midnight, 000000106813 milliseconds after the Progress session that caused the record insert was started. The next two 4 digit strings, 6259 and 7956 are randomly generated numbers, and the .d finishes off the string. With this information you can tell exactly what records were inserted, deleted or updated, and if you wanted to get fancy you could use these files to keep multiple SQL Servers in-sync with your Progress Server. Note: Don’t forget to clear these log files out occasionally or they will eventually fill up your hard drive. That’s it. Your DataLink program is now ready to run. If you created the VB Form version of the DataLink programs then start it up and let them run. They will watch for the small text files created by the Progress database and turn them into records in the Sytelview database on your SQL Server. If you created the DataLink program without the VB Forms, then set them up to run on your Windows Task Scheduler at whatever frequency you require. VERY IMPORTANT NOTE: DON’T USE DATALINKS WITH YOUR PRODUCTION DATABASE UNTIL YOU ARE SURE THAT YOU KNOW WHAT YOU ARE DOING AND HAVE THOUROUGHLY TESTED YOUR DATALINKS IMPLEMENTATION. |