SQL Server File Polling

[theomen]

Hi All,

I've been given a project to work on which involves taking data from various sources (mainly Excel) and putting them into a single database. Excel files will be automatically dropped into a shared folder on a regular basis (probably monthly), but everything needs to be automated with no user involvement (except for retrieving reports).

My question is, is it possible to setup file polling in SQL Server, so that as soon as a file is put into the shared folder, SQL picks it up and performs whatever tasks are required?

Another option I am looking at is to write a VB program that just checks the folder every 5 minutes or so, and if it finds a new file, it calls the stored procedures to do the processing, but I'd prefer to keep everything in SQL Server.

Any help would be much appreciated (its my first project in a new job so I'd like to get it working ).

 

[SajidAttar]

Hi,
You can use DTS Pacakge to perform this task regular basis. You just need to create DTS Package with following steps and has to schedule it to run daily as a job(If you are sure that the Excel file is being dropped in specified folder on a particular day of every month then you can schedule the job to run on that day also).

Create two connections in your DTS Package (one for Excel sheet and another for SQL)

Your DTS Package should have Data Transform task between Excel Connection and SQL Connection.

You also have delete (or move the excel file to some other folder for backup) as second step of the above job, so it wont uploaded next run.

Hope this helps you. If you have any question feel free to post.

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[druer]

Sajid,

Could you further explain whether your solution will do the part he was looking for, which is automatically invoke itself when the files show up? I haven't done much with DTS packages but I thought that if a package was set as a scheduled job when it kicked off if the file wasn't there then the job would just end and fail.

My suggestion was going to be to have the DTS job in place and schedule it to run hourly. So if the file is dropped in place it would be caught within the hour, but I didn't know of a way to have it startup immediately when the file showed up.

 

[SajidAttar]

Excel files will be automatically dropped into a shared folder on a regular basis (probably monthly), but everything needs to be automated with no user involvement (except for retrieving reports).

Thats why I suggested to schedule the job to run daily /Specific day.

First step of the DTS Package should check the existance of Excel file (the file name should be the same every time and if it is defferent everytime then need to use ActiveX Script). (And proper error handling should be done)

If the data of Excel sheet is Important and required to affect immediately then need to schedule to run with short difference (every 5/10 mins).

or
DTSRun Utility of SQL Server can be used to run the DTS Package from Command Prompt. for this BatchFile should be written which will check the Folder and execute the DTS Package as soon as the file dropped in.

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[maswien]

Just some thoughts, not sure it helps or not.

DTS will work if it's possible to check if the file exists, but how can it know which one is the new file?

If the excel file will be dropped in monthly, is it possible just use the month number as suffix of these
excel files when they are generated? So there is a convention for the file name of these excel files:
for example, hr01.xls, hr02.xls,...hr12.xls, it01.xls, it02.xls,...it12.xls...

There will be a record for the imported month stored somewhere, then the DTS can access that whenever it
searching the folder for the next month data need to be imported.

 

[theomen]

Thanks for the advice.

I've actually got a vb.net application running now which successfully picks up new files, before moving them into a backup file and calling a stored procedure to copy the data into the database.

However, I've now come up with another problem. I want a stored procedure to import all data from an excel file into a new table. This works fine if I hard code the spreadsheet name and worksheet name into the procedue as follows:

***start of code***

SELECT *
INTO #Format1Import
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\myfile.xls;HDR=YES',
'SELECT * FROM [myworksheet$]')


***end of code***

but if I try to use the filename and worksheet name passed into the SProc as parameters as follows:

***start of code***

SELECT *
INTO #Format1Import
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=@filename;HDR=YES',
'SELECT * FROM [@worksheet]')


***end of code***

I get the following error:

Server: Msg 7399, Level 16, State 1, Procedure usp_FileFormat1_Import, Line 8
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine could not find the object '@worksheet'. Make sure the object exists and that you spell its name and the path name correctly.]

I've also tried adding $ onto the end of the worksheet variable (ie. [@worksheet$]).

 

[SajidAttar]

You need to concatenate the values to get it worked.

SELECT * 
INTO #Format1Import
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
        'Excel 8.0;Database=[b]' + @filename + '[/b];HDR=YES', 
        'SELECT * FROM [[b]' + @worksheet + '[/b]]')

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell