0

I have an SSIS package that has been working without issue for some time, but due to changes in our company infrastructure, we have had to make some changes.
In simple terms, the SSIS package reads a table containing a list of files that will require import, and loops through the list importing the contents of each Excel file via multiple Data Flow Tasks. Each series of tasks (one series per Excel worksheet) are held within Sequence Containers, and when the final series of tasks completes, the record in the table is deleted, and where the source of the loop is a copied file (some files are stored in SharePoint Online so we have a process to copy them to a fixed temporary location), the file is finally deleted by a System Task. For reasons yet unknown, the final sequence is failing, and the 'Completion' arrow from the sequence to the final two steps is correctly deleting the record from the database table, but the final Delete File task is failing saying 'The process cannot access the file 'abc' because it is being used by another process'
There are two connection managers, one that relies on the data having headings, and one where it doesn't. Both have the property RetainSameConnection set as True, and are both used throughout the process, based on the data being imported.
Is there a way to force the connection closed before moving to the Delete File task?
Thanks
Martin

MartinS
  • 111
  • 1
  • 14
  • This is a guess, but if you are using a script component to process the excel file then you must either .Close() your connection or access your file inside a using{ } block. If not the connection remains open until the package completes. – KeithL Jul 14 '21 at 13:04
  • Thanks @KeithL. I ended up changing the RetainSameConnection to be False and that allowed the file to be deleted, so it was due to the change to the connection with headings and that not being closed on failure. By setting it to False, the file now deletes. – MartinS Jul 14 '21 at 13:29

1 Answers1

0

Whilst it will take slightly longer to process the list of files, by setting RetainSameConnection to False for both connection managers, regardless of whether the process fails or succeeds, the file is deleted as it's not being held open by the connection.

MartinS
  • 111
  • 1
  • 14