1

I need to import a flat file daily. The file changes its name every day. After the file is processed, it needs to be moved to another folder.

I noticed I can schedule jobs in the SQL Server Agent, and that I can tell it to run every hour or so and that I am able to add CMD commands to it.

The solution I found was to run a script to check if the file exists, since the folder should be empty or have at least one file.

If the file exists, the script renames the file to one used in the SSIS package and then it runs the SSIS package.

After the whole thing is done, it should rename the file again based on today's date and move it to another folder.

If the file does not exist, then it should do nothing and wait another hour or so to run again.

What's the best solution to this scenario? Is the script a good idea? Maybe is it possible to add the if/else -for the file exists- into the SSIS package? Or even make the script run from the SSIS package itself instead of adding it to the Server Agent?

EDIT:

It seems I was a little naïve, it's possible to run VB scripts from the server. Would that be the recommended solution? It does solve my problem, but I'm just wondering if it's a good idea.

Johnny Bigoode
  • 578
  • 10
  • 31
  • Have you looked at an SSIS file watcher scenario? SOme implementations use WMI event watchers http://microsoft-ssis.blogspot.com/2010/12/continuously-watching-files-with-wmi.html – Brad D Dec 05 '14 at 14:05
  • You receive a file with a variable name (assume it has a date or something in it). You rename it to a standard name. Process it. Move it and add the date processed into the file name. That about sum up the situation? – billinkc Dec 05 '14 at 15:46
  • Yes. I found a few guides under the WMI event watcher thing. But now I'm having issues with the WQL query. This has also been awnsered elsewhere: http://stackoverflow.com/questions/21436377/how-to-execute-ssis-package-when-a-file-is-arrived-at-folder and in more detail here http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/90571/ – Johnny Bigoode Dec 05 '14 at 18:36

1 Answers1

1

This solves all my questions:

http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/90571/

Johnny Bigoode
  • 578
  • 10
  • 31