July 5, 2007 at 2:39 pm
I recently came back to SQL Server from Oracle and have had very little interface with Integration services. So any help will be greatly appreciated>
My question / Task. We scan photos and documents by patient into a folder named with the patients id, I need to test whether the application database list of patients (by id) have corresponding matching folders on a DFS. Can this be done by creating an SSIS package?
Any thoughts or suggestions?
July 5, 2007 at 3:36 pm
I'm sure you can do it via an SSIS package, but honestly I'd rather do it using sys.xp_fileexist.
Try doing:
EXEC master.sys.xp_fileexist 'C:\Folder\Myfile.ext'
July 5, 2007 at 5:05 pm
I'd do it the other way around. List all files that are in the directory(ies) and send this into a table. Then you can do a left join to find missing pictures or an inner join to set some flags in the patients table.
This is the main difference between sql server and oracle. Sql server is built to work with data sets, Oracle is built to deal with one row at the time. Not that any principal is better than the other, it's just the way it works.
July 6, 2007 at 8:28 am
Thanks, will try both, probably the table idea will be best since I am dealing with 5 - 10 thousand folders
July 6, 2007 at 10:09 am
Definitly. My idea will be the best approach in this context.
You can use fileexists as the best approach in the case where you only want to check if 1 file exists. EX : If NOT FileExists (FilePath) then CreateFile END ... run rest of code. In this case there's no point in listing all files to see if one exists.
I don't see many cases where you'd need to do this in production. However, I can imagine a few in administration.
But in your case I'd definitly use a table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply