Creating Syteview DataGrabbers

the table before proceeding.

To do this select the Create Table icon and copy and paste it just to the left of the original Create Table icon.



Double click the new icon and change the Description from Create Table to Drop Table and change the SQL Statement to DROP TABLE [Syteline].[dbo].[your table name] and click OK.



Finally select the Drop Table icon you just created by clicking on it, then hold down the Ctrl key and click on the Create Table icon .  Make sure you selected the Drop Table icon BEFORE you select the Create Table icon.  Now pull down the Workflow menu and select On Completion.

You should now see an arrow going from the Drop Table icon to the Create Table icon.



Finally, pull down the Package menu, select Save and you are done.

Done creating the DTS Package that is.  Now if you want to be able to create a Visual Basic program (a Syteview DataGrabber) that executes the same steps you have just specified, run the DTS2VB.exe program in the DTS-to-VBCode sub-directory.

Question: Why would you want to create a Visual Basic program that grabs tables and records from the Progress database and puts them in the Microsoft SQL Server when you already have a nice DTS Package that does the same thing?

Well, for one thing, you can use that VB program to create an executable that you can call from a batch file that imports the table and records at a specified time using Windows Task Scheduler.  You could automate the DTS Package using SQL Server Agents, but by using the Task Scheduler you can change the schedule without opening the SQL Server Enterprise Manager and you might feel a lot more comfortable letting someone else change the schedule using the Task Scheduler than you would letting them mess around inside the SQL Server Enterprise Manager.

Another, and maybe a better reason is you can modify the source code of the VB program to do almost anything else you might want while the records are being imported.  Like maybe sending an email when it sees a new customer order, or… well that’s up to you.  When you have the source code you can do almost anything!  Or you can just schedule the DTS Packages and forget the next steps.

If you do want to create a new Data Grabber from the DTS Package then run the DTS2VB.exe program in the \DTS-to-VBCode sub-directory in the Syteview installation directory on your SQL Server.

The following window will pop up:



Fill in the name of the DTS Package you just created.  GetCOs in this case.



And then click OK.

On the next screen, fill in the Progress Username you will use when importing records from the Progress table if you have enabled security on your Progress database.  Leave the Username blank if you have not enabled security on your Progress database or if you are using a Password file.



On the next screen, fill in the Progress Password you will use when importing records from the Progress table if you have enabled security on your Progress database.  Leave the password blank if you have not enabled security on your Progress database or if you are using a Password file.



Note: The Password file mentioned above is located in the \DataGrabbers\Password sub-directory and is named ProgressDB.Password.txt.  If this file exists then each DataGrabber in the \DataGrabbers directory will use the username and password listed within when connecting to the Progress database to retrieve records.   Using this file allows you to change the Progress username and password without having to recompile the Visual Basic code that makes up your DataGrabbers.

Note: If you have not enabled security on your Progress database then you should rename or delete this file before creating your DataGrabber programs.



On the next screen, fill in the Microsoft SQL Server Username you will use when connecting to the SQL Server if you are not using NT Integrated Security.  Leave the username blank if you are using NT Integrated Security.

Note: You really SHOULD be using NT Integrated Security to connect to your SQL Server.



On the next screen, fill in the Microsoft SQL Server Password you will use when connecting to the SQL Server if you are not using NT Integrated Security.  Leave the password blank if you are using NT Integrated Security.



After clicking OK you will (hopefully) see the following screen:



Assuming you see this screen, you will find the source code for the new program that was just created (in this case it’s GetCOs.txt) in the \DTS-to-VBCode directory.  Highlight the program and right click it and select Open With.  When the Open With window pops up select Visual Basic 6 and click OK.

Note: you might want to move the GetCOs.txt file to its own sub-directory before using it to create the Visual Basic program.  We’ve put each of the GetXXXs programs that come with Syteview in their own sub-directory under the \DataGrabbers directory.

Visual Basic will open up with the GetCOs.txt in the editor.



Pull down the Project menu and select References.  In the References window checkmark the Microsoft DTSPackage Object Library



Click OK to close the window.

Pull down the File menu and select Save Project As.  Give the project a name similar to the DTS Package name.  For example: GetCO.vbp

Next, pull down the File menu again and select Make Exe.

You have just created a new Data Grabber that will import a table and records from your Progress database and put it in the Microsoft SQL Server.  Way to go.

To run the Data Grabber, put it in a sub-directory of its own and copy an INDEXES.TXT and a PERMISSIONS.TXT file from the sub-directory of a similar Data Grabber.  You could use the INDEXES.TXT and PERMISSIONS.TXT files from the \Syteview_SQL\DataGrabbers\GetCOs sub-directory on the Syteview Installation CD-ROM in this case.

Then edit the Permissions and Indexes files to grant the permissions and create the indexes you want on the table the new Data Grabber will be creating.

Finally, pop out to the DOS command line, change into the sub-directory with your new Data Grabber in it and run it.

Easy, wasn’t it?