Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Robowski
Posting Yak Master
101 Posts |
Posted - 2013-11-20 : 06:07:45
|
Is it possible to query a windows folder via t-sql?A table will hold the possible file names of a set of files we receive each day. Is it possible to query the folder and match on the file names stored in t-sql to what is in the folder?Or will this need something like VB/excel? |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-20 : 07:56:16
|
you can use xp_cmdshell for that if required to use in t-sql. But xp_cmdshell would be disabled by default due to security concerns unless you're dba you cant enable it. so better approach would be to use SSIS for this.SSIS you've for each loop with file enumerator which can be used to iterate through the folder (you can make folder also dynamic using expressions) and then you can use a execute sql task to retrieve list of filenames and compare the files against it.see here for illustration on use of for each loop for iterating through files.http://visakhm.blogspot.com/2012/05/package-to-implement-daily-processing.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-11-20 : 08:37:39
|
Another solution you might consider is a powershell script. Being a scripting language, it is readable (i.e., the deployed code is not compiled code), and it is designed to work with file systems and SQL Server. |
|
|
|
|
|