"Real timing" data from legacy system into SQL 2000

Question

"Real timing" data from legacy system into SQL 2000

teekay-101299

Mr or Mrs. 500

Points: 585
More actions
November 26, 2007 at 7:13 am

#70538

We want SQL DTS to import a data file into a table when it appears in a directory in a server. Meaning, a legcay batch process will save a file into a directory on the SQL Server. Once the save is completed, I would like a some kind of listner or trigger? to pick up the file and import all records into a table(s) in SQL 2000. I would like keep this as close to "Real time" as possible.
We would be importing less then 100 records with about 25 columns of data. The average import would be probably around 15 records.
Is this possible?
A little more detail on this... this process is taking orders from a legacy application an importing them into SQL for a web app. The legacy does not have a usable ODBC and the data is not normalized. Our plan is to normalize the data in SQL through this import process and provide access to the data via a web app. The web app should display an order that was created in the legacy sysytem as quickly as possible.
Any recommendations would be great.
Thanks
TeeKay

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Norman Kelm SSCommitted Points: 1922 More actions · Answer 1

TeeKay,

This can be accomplished using an ActiveX Script Task that has a script that uses that FileSystemObject to perform a directory listing. It could be done in a while loop and return DTS success to move on to the transformation and do the load when the file is found.

The SQL Agent job would have a second step that does nothing (a t-sql command SELECT 1) that will send it back to the first job step on success or failure.

The other method is to have the ActiveX Script Task enable and disable the next task to control package execution path.

If you have the option to use Integration Services for this, do. There is a File Watcher Task available which will make this process very simple.

http://www.sqlis.com/23.aspx

Norman

DTS Package Search

http://www.dtspackagesearch.com/

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 736255

November 26, 2007 at 8:47 am

#754100

I'd vote for the File Watcher task. Works well.

teekay-101299 Mr or Mrs. 500 Points: 585 More actions · Answer 3

From what I have read, File Watcher is for SQL 2005 and NOT SQL 2000.

Norman Kelm SSCommitted Points: 1922 More actions · Answer 4

Yes, you are correct. The File Watcher is only for Integration Services/SQL 2005. I was just letting you know about it. It makes watching for a file simple. If you have a 2005 installation that you could use for this project, this would be the one to use it.

Norman

DTS Package Search

http://www.dtspackagesearch.com/

teekay-101299 Mr or Mrs. 500 Points: 585 More actions · Answer 5

Thanks Norman. I have built the SQL 2005 and tested the FileWatcher process and it all works. As I mentioned before, I want to load the data once a file is available in a specifed directory. You recommend using ActiveX. Do you think this will have any impact on performance over a period of time? Are there any other alternative that some may want to propose?

What about scheduling the FileWatcher task to run every minute or so?

I am looking for other alternative and hear about what others have done to create as close to "real-time" from txt files into SQL 2005...

Thanks in advance

Norman Kelm SSCommitted Points: 1922 More actions · Answer 6

Teekay,

I only recommended ActiveX as an alternative way with DTS since a file watcher task is not available in 2000/DTS.

Now that you have the FileWatcher and SQL Server 2005 you are "done". Process the file with the normal SSIS components.

I use this to allow user to manually generate an Excel file and copy it to a public share.

The FileWatcher is watching the public share and only triggers on a specific file name.

The files is processed and load into a SQL Server.

In order to restart the package, the job that runs the SSIS package has two steps.

Step 1 Run the FileWatcher SSIS package. Advanced properties On Success go to next step. On failure go to next step.

Step 2 T-SQL Step with SELECT GETDATE() Advanced properties On Success go to step 1. On failure go to step 1 .

Step 2 is just a dummy step to get the job to go back to step 1 and never end. Set the job to start on agent start up.

Norman

DTS Package Search

http://www.dtspackagesearch.com/

teekay-101299 Mr or Mrs. 500 Points: 585 More actions · Answer 7

Norman,

Thank you very much for your replies. This is exactly what I was looking to accomplish.

Thanks again for your efforts.

TeeKay