SSIS Excel connection is creating another temporary file and interfering with the FileSystemWatcher
-
Hi All, I have a File System Watcher, which is monitoring file drops and renaming etc, which keeps the file information into a table, I have another application which executes the file related SSIS packages, and these two applications are Asynchronous. The problem is the Excel Connection Manager in the SSIS Package, when its reading the files, its creating another temporary file which is unnecessarily creating a record in the table. Can anybody please help me in how to overcome this scenario, I did a little bit by keeping a file exists check in the SSIS package that if the file exists or file already ran I am not going to run the package again on the File but still a record entry would be there on the monitors table which comes because of the Excel connection manager opening and reading the File. Any help would be very helpful, a code snippet, a link or suggestion anything helps, thanks in advance :)
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi All, I have a File System Watcher, which is monitoring file drops and renaming etc, which keeps the file information into a table, I have another application which executes the file related SSIS packages, and these two applications are Asynchronous. The problem is the Excel Connection Manager in the SSIS Package, when its reading the files, its creating another temporary file which is unnecessarily creating a record in the table. Can anybody please help me in how to overcome this scenario, I did a little bit by keeping a file exists check in the SSIS package that if the file exists or file already ran I am not going to run the package again on the File but still a record entry would be there on the monitors table which comes because of the Excel connection manager opening and reading the File. Any help would be very helpful, a code snippet, a link or suggestion anything helps, thanks in advance :)
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
In your setup of the file system watcher, did you declare a filter so that it is only looking for .xlsx ? Wouldn't that eliminate the notifications of the Excel temp files ? https://msdn.microsoft.com/en-us/library/system.io.filesystemwatcher.filter(v=vs.110).aspx
-
In your setup of the file system watcher, did you declare a filter so that it is only looking for .xlsx ? Wouldn't that eliminate the notifications of the Excel temp files ? https://msdn.microsoft.com/en-us/library/system.io.filesystemwatcher.filter(v=vs.110).aspx
I did but the problem is the temp files are going to be created with $ prefix but the extension is same and another thing is that the temp files is going to be renamed back to the original file I think that's the reason why, it was hard for me to catch it and fix it. I tried it in hard way but only got 90% success, the way is to not insert any record into the monitoring table if same file name and location file comes within 15 minutes of previous drop then don't insert it into the monitoring table, but still for some SSIS package may take more that 15 minutes to read, then still those files are showing up in the monitoring table. If there is any easy and proven approach that would be very helpful, thanks for the help buddy:)
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."