How to create a DTS package in SQL 2000

[dpu]

Hello I am hoping someone can help me out. I need to create a DTS package that will automatically update information on another system.
The issue that I am facing is that we have recently implemented a software in which we are manually entering patient information into it. This can lead to several keying errors. On the other hand we have an existing SQL DB that already has the information so I would like to create a DTS package that will export the patient information to a flat file whereby this application can grab the file and read from it and update it's database with this information.
In addition I would like to have that package automatically send information over to the other software when new patients are added to our database.

 

[VDavid]

Hi,

The way I see your problem, there are different approach depending in the data platform of your new software.

1-If the new software use or doesn't use the data from a MSSQL database:
1.1- For the historical information, create a one-use DTS package, copy the information in your new software. For the new information, create a trigger to update your system every time a new record is added.
1.2- For the historical information, create a DTS package to copy the information in your new software. For the new information, create a DTS package to only copy the new records to your software and program it to run at certain interval, this will require some kind of flag to know which one have already been copy and which haven't.
1.3- Another way is to create a text file and update your software, but, if you can access your new database from a DTS then, it will be like adding a new layer that really doesn't bring any benefits.

2-If the new software use the data from a MSSQL database:
2.1- Create a view of the patients records and use it to show the information in your software. If you are only viewing and not updating then, this will be a good way to do it. Another plus is that you would not have the same information in two separate locations and the updates in the principal system will be real time to your software.
2.2- If you are going to update the data then use a stored procedure to obtain the historical records and the new records as well.

As for the DTs itself:
First I will suggest to look at the FAQ

http://www.tek-tips.com/faqs.cfm?fid=962

But to get you started:
1. In Enterprise Manager, create a new DTS
2. Place a Microsoft OLE DB Provider for SQL Server object. Configure it to where the patients records are. This will be your source connection
3. Place another connection and point it to your new database. The type of this connection will be determined by the location of your data, if it is in MSSQL you can use the same as step 2, if not find the best suitable for you, usually using a ODBC driver.
4. Select your source connection, and click on transform data task icon (the one with an black arrow on it), then select the other connection.
5. In the transform data task properties select your source data (tables, views, sql query), this is where the information is. In the destination tab select where you want the information to be copy. And in the transformations tab, you will be able to change or adjust the data as required for your new software.

I hope I make myself clear enough.

David.