2

I'm not new to SQL Server but am new to Integration Services, so I want to understand if the following requirement is fully achievable in SSIS, or if I will need to consider some level of C# development to supplement:

We have 25 Azure VM's running Windows Server 2016 Datacenter - on each VM we have thousands of log files in different folders. We need to have all these folders monitored and upon creation of any new file with a certain string present in the name, we want the contents of the file exported to a table in our Azure SQL Server 2017 database.

Is this kind of custom logic configurable in an SSIS project, or is SSIS more geared toward static definitions of file folders/filenames?

Hadi
  • 36,233
  • 13
  • 65
  • 124
Stpete111
  • 3,109
  • 4
  • 34
  • 74

1 Answers1

1

I don't think that you can implement a folder watcher within SSIS package. But you can achieve this in different ways:

(1) - using a folder watcher

You have to develop the SSIS packages you need to import data, then you must develop a folder watcher using C# to read the changes in folder and if the file name meet the requirementes you should run dtexec utility (using shell) to run the relevant SSIS package.

(2) - using SQL agent job

You can configure an SQL server agent job to check the file changes periodically and run the SSIS package when required.

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Hadi, thanks for the info. Azure SQL Server unfortunately doesn't support SQL Agent, so that eliminates #2. Regarding the other option - aside from folder monitoring, can SSIS be configured with custom filename requirements? I see in your answer that you refer to it, but I can't tell if that is also something that would have to be written into the C# part. I ask because if I can eliminate the folder monitor requirement, I wonder if SSIS could address the sole remaining requirement of picking up only files that have a certain kind of name. – Stpete111 Jan 21 '20 at 12:46