1

I am importing the contents of a bunch of excel files into a database. Basically importing all the files in a folder on a shared network-drive. I am using a for each file enumerator for this. Works like a charm. However, if one of those files is opened by another user (which could be anytime) my procedure fails. The procedure fails on the Excel source connection in the dataflow. Default Excel source connector.

I would like to either

  • just skip the file, continue with the next file (next iteration of loop) and try to import it on the next run
  • somehow open the file readonly (like excel itself allows me to do)

Preferably no script task solutions (not much scripting knowledge available in team) but if it has to be scripting, so be it. I can comment it to biblically proportions so the team will understand.

Henrov
  • 1,610
  • 1
  • 24
  • 52
  • When you run this package, do you run it directly or is it invoked by a separate controller/orchestrator package? – billinkc Apr 21 '23 at 15:45
  • It is the only package, no children or parents involved – Henrov Apr 22 '23 at 17:10
  • 1
    I know nothing about SSIS, but can easily do this in C# with `try` `catch`. Maybe this helps: https://zappysys.com/blog/ssis-check-file-locked-wait-file-unlocked-c-script/ – M.Sqrl Apr 23 '23 at 21:41
  • 1
    https://stackoverflow.com/a/937558/1459036 You would have to use C# script task and do something like here: https://stackoverflow.com/a/937558/1459036 Then build something else in to mark it as locked as you are looping so you can go back and do that one again. – Brad Apr 25 '23 at 12:54
  • @brad Can you please enter this as an answer so I can award you the bounty? – Henrov Apr 26 '23 at 08:30

1 Answers1

1

From my comment was asked to post this as solution:

You would have to use C# script task and do something like here: stackoverflow.com/a/937558/1459036 Then build something else in to mark it as locked as you are looping so you can go back and do that one again.

Brad
  • 3,454
  • 3
  • 27
  • 50