June 23, 2008 at 6:08 pm
Hello,
I have the following requirements. A client is to send a file via ftp to a directory on my server (same box as my database).
I need to import the data into my SQL Server database.
The data import part I am not to concerned about it.
The part I need help with is running the job. Because the file can arrive anytime throughout the night I am considering using a file watcher but research has mixed reviews.
My concerns are ensuring the file is done uploading (the file is only about 2 megs) but if I look for the file and it is there but not done uploading I am worried my SSIS package will fail.
Thoughts?
June 23, 2008 at 6:14 pm
lreid (6/23/2008)
Hello,I have the following requirements. A client is to send a file via ftp to a directory on my server (same box as my database).
I need to import the data into my SQL Server database.
The data import part I am not to concerned about it.
The part I need help with is running the job. Because the file can arrive anytime throughout the night I am considering using a file watcher but research has mixed reviews.
My concerns are ensuring the file is done uploading (the file is only about 2 megs) but if I look for the file and it is there but not done uploading I am worried my SSIS package will fail.
Thoughts?
You say the file is ftped to a directory on your server. Do you have an FTP service running there or is this done through virtual folders (I call it smoke and mirrors)? If you have an FTP service running on the database server, how active is it? Also, how often is the file sent to you, once a night?
😎
June 23, 2008 at 6:44 pm
Its done through the FTP service in IIS. The file will either arrive once per night or once per month. The client has sporadic updates but as of now is planning on sending the entire file even if the data is not changed.
The update is small and simple so thats not an issue.
June 23, 2008 at 7:27 pm
Okay. I know the FTP is part of IIS. What I was asking is this setup on the Database Server or on a seperate dedicated FTP server, using virtual folders to allow the file to actually be loaded onto the database server.
I have an idea for you, but I need to know what type of environment you are working in.
Here is what I had (and is still used) at a previous employer. We had (have) two servers. Both with SQL Server 2000 (remember, this was several years ago when I did this) installed. On one server we also had IIS installed including the FTP service. To protect the guilty I will call these servers Server1 and Server2. Server1 is going to be the main database server, while Server2 is the FTP server.
On Server2, I setup a database called FtpLog, with a table called FtpTransfers. The FTP Service was configured to log all data transfers to the SQL database. Although Server2 was supposed to be for development, it wasn't and the only thing SQL Server did on Server2 was log data transfers. This was also an internal FTP server, so we had excellent control over who could send files to the server. Using virtual folders, we mapped directories from Server1 to Server2. When files were FTPed to Server2, it appeared as if the files were sent to a directory on Server2, but actually went to Server1 (I just repeated myself).
I then setup INSERT triggers on the table FtpTransfers in the FtpLog database that would check if the transfer was good, and if so it would start the import process (an unscheduled job on Server1) on Server1 using the sp_start_job stored procedure in the msdb database on Server2.
If you have the FTP process running on your database server, you could do something similiar, but I would use a second instance of SQL Server 2005, with limited resources assigned so that it would not impact the primary instance on the server.
The other option is to create a continously running job that runs your SSIS package, and code a looping process that continuosly loops until the file arrives, and then processes the file.
I also think that you may want to check out http://www.sqlis.com. If I remember right, they might have a FileWatcher task for SSIS.
😎
June 23, 2008 at 7:47 pm
Interesting... how is this done: The FTP Service was configured to log all data transfers to the SQL database.?
June 23, 2008 at 7:52 pm
I see it here: http://support.microsoft.com/kb/245243
Excellent. Does it log when the file is done uploading? Or when it starts?
Can I use a trigger to call a DTS package? I was going to upgrade to SQL 2005 but I might to have to now 🙂
June 23, 2008 at 8:38 pm
Yes, the FTP server was (is) configured to log all activity to a SQL Server database. It logs everything. You would have to set a trigger to check for a successful transfer of a specific file.
No, you don't want to run the SSIS package from the trigger. All you want the trigger to do is start the job that does the actual work (runs the SSIS package).
😎
June 23, 2008 at 8:46 pm
Ok... i want to use a DTS package instead....
Same thing though... start the JOB? What do you mean by job?
June 23, 2008 at 9:00 pm
Read about jobs in BOL. Yes, you want to run a job. A trigger needs to be quick. If you start a job using msdb.dbo.sp_start_job, it returns quickly after starting the job requested. You need to handle the possibility of a failure to start the job in the trigger. If you don't, you'll lose the record that triggered the processing.
With that, I'd still look at upgrading to SQL Server 2005. One, you can use the try/catch syntax available in SQL Server 2005. Also, you could just install a seperate instance of SQL Server 2005, and not touch the current instance where the database ou are importing the file.
😎
June 23, 2008 at 9:26 pm
Awesome....
Thanks for the tips....
Does FTP logging use the same table as HTTP logging I can't seem to get that working 🙂
June 23, 2008 at 9:45 pm
Not sure. I never had a reason to log HTTP (Web requests). You'll have to research that one on your own.
😎
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply