0

So I have a SSIS package that reads from an excel file and populates a Sql server database. It assumes that the file will be updated monthly, and consequently, the data pull to update it is also done monthly.

However, as this excel file needs to be updated manually, there may be a case in which the user forgets to re-upload a new excel file. Naturally, I want to detect if that is the case. How would I be able to do this?

The best that I could think of was to use a script task to alter the excel file's sheet name to something like "already processed" after the data pull, and check for that condition at the beginning of the task. However, this seems like a work around solution that's overly complicated. Any ideas for better ways to do this?

Improve this question

1 Answer 1

Reset to default
1

You could have a meta data table that stores the file name, processing date, and last modified date. Then you can have your job run daily and identify whether the file's last modified date is greater than the stored value.

If the file hasn't been modified in 32 (arbitrary number but base it on business tolerance) send a notification to you and/or the business process owner saying no new file detected.

This won't cover cases where they've opened the file, changed nothing but clicked save none-the-less (or Save As and overlaid the file). At that point, you'd get to write actual change detection. Which could be a weird kind of fun but surely there's more important priorities for the business.

I'd advise against changing the tab name. Lots of reasons for it but the easiest one to enumerate is technical/license based. Programmatically renaming an Excel worksheet is an easy technical change. But, to overcome a business process deficit, you're going to take a dependency on the Excel object library. You get to read/write to Excel via SSIS for free. Reading/Writing to Excel from a Script Task is going to use the COM interop functionality. To use that, you need to have Excel installed. Excel on a desktop, yes. Excel on a Server, no (it kills your uptime + costs you an license for the Office installation)

Yes, I am quite aware of nuget packages, the ACE library etc that are free and can provide interactivity with Excel but then that gets us into server installation, GAC requirements, 32 vs 64 bittedness discussions etc.

Improve this answer
3
  • 1
    Solid advice. I would recommend checking & storing the file hash to determine if the file has been updated, rather than relying on the last modified date. Obviously, this may have impacts if the file is extremely large. An even better solution would be some kind of Event Grid (e.g. Azure Blob Storage) that notifies you if a file is modified.
    – Kane
    Commented Oct 9, 2020 at 16:17
  • Good thoughts on the file hash vs last modified date.
    – billinkc
    Commented Oct 9, 2020 at 18:06
  • 1
    The challenge I've had with file event driven products (and it's been more than a decade since I was last burnt/tried) is that "something happens" such that the trigger doesn't fire (product hangs, listener doesn't hear it, some crazy file system issue, etc) and my dependent process never knows it should have done something. It's only when the business owner asks "why is this data stale?" that I know to go looking. We ended up writing a monitoring process to "watch the watcher" as we knew the month end data should be there by 5th business day so on the 8th, a job would compare file to database
    – billinkc
    Commented Oct 9, 2020 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.