I'm trying to create a package that imports excel files into a database using SSIS.
As the operation has to perform this regularly and the file names follow a convention but are not the same, and equally the sheet/tab names are not always the same, the SSIS package is set up as follows:
Main Container -> First For Each container (call it FE1) Obtains filenames (assigns to a variable) -> Second For Each Container (call it FE2) Obtains worksheet name and starts the process to import.
What I have done is create a "failure" precedence constraint from FE2 to a file system task process in FE1.
The idea is that the file move is done if the import is unsuccessful for whatever reason.
(once it works I'd like to create a "success" process that moves the file to the archive folder)
The file task process works when there is only one "for each container" (i.e. not nested the way it is now) but it fails when all the processes are in the nested container citing "file in use". I'm assuming this is because the first for each container is locking the file, hence why I moved the file task process to the first for each container and used a precedent control.
Any help and advice much appreciated.
For the benefit of anyone else who may have the same problem:
for love nor money could I get the excel connector to release the files, even when the move file task was outside of the loop.
In the end I recorded the files that were moved into a table in the DB and then executed a second package that contained the move file task and would iterate through the table rows with the list of successfully imported (and failed import) files and moved them to their destination based on fail/success flag.
It was the only way I got to successfully make this happen.
When having to iterate through the worksheet of each excel file (i.e. two excel connections effectively) SSIS would not release the files so I forever received the file in use error and failure.