question

jflemi02 avatar image
jflemi02 asked

SQL Server 2012 : How do I compare my query (List) to file names in a file directory?

I'm writing query to a develop a list of programs used for production. Then use that list to check if they exist in a file directory. If not, trigger an email notification.

sql-server-2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
WRBI avatar image
WRBI answered

There are a few ways that I can think of, I'll leave you to Google up some tutorials as there are plenty out there:

  • My go to option SSIS ForEachLoop which could read the filepaths and filenames into a variable and then you could compare them against another variable populated by a SQL Query. SSIS can then trigger an email once it has collated an list of missing programs, or do nothing, saving your inbox.
  • A .net console package, it has all the functionality of connecting to SQL, reading directories and sending email.
  • PowerShell, again it has the capabilities that you're looking for.

I'd say SSIS as I assume (doh!) that you're already running packages in your estate.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.