locked
SSIS Foreach Loop Container Logic RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi all,

    I want to load files and delete them afterwards. Can anyone tell me what the logic behind this container is?

    In case I put a Data Flow Task in the container (which loads the file into my database) and a File System Task which is going to delete the file immediately after loading. Does the container know which file was loaded and passes this one through to the File System Task for deletion?

    I definitely want to make sure that no data gets lost and that the container really deletes the exact file it was reading before.


    Thanks for your help in advance!

    Regards

    Laumen

    Wednesday, September 10, 2014 10:40 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    The logic of the file system task will take the current variable you have passed to it, so it'd work.

    If there are several files to load and delete and (obviously) have the same structure I suggest you concatenate them before trying to load them.

    • Proposed as answer by Charlie Liao Monday, September 15, 2014 2:00 PM
    • Marked as answer by Charlie Liao Wednesday, September 17, 2014 1:14 PM
    Wednesday, September 10, 2014 10:49 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    If there are too many files e.g. 1000 files, I'd concatenate them into ONE file and fast load that file into the DB.

    You can use a powershell command to combine them or just write a script task in c#/vb.net.

    • Proposed as answer by Charlie Liao Monday, September 15, 2014 2:00 PM
    • Marked as answer by Charlie Liao Wednesday, September 17, 2014 1:14 PM
    Wednesday, September 10, 2014 11:51 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    The logic of the file system task will take the current variable you have passed to it, so it'd work.

    If there are several files to load and delete and (obviously) have the same structure I suggest you concatenate them before trying to load them.

    • Proposed as answer by Charlie Liao Monday, September 15, 2014 2:00 PM
    • Marked as answer by Charlie Liao Wednesday, September 17, 2014 1:14 PM
    Wednesday, September 10, 2014 10:49 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Thanks for your quick response.

    What do you exactly mean by 'concatenate them before trying to load'?

    Wednesday, September 10, 2014 11:42 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    If there are too many files e.g. 1000 files, I'd concatenate them into ONE file and fast load that file into the DB.

    You can use a powershell command to combine them or just write a script task in c#/vb.net.

    • Proposed as answer by Charlie Liao Monday, September 15, 2014 2:00 PM
    • Marked as answer by Charlie Liao Wednesday, September 17, 2014 1:14 PM
    Wednesday, September 10, 2014 11:51 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thank you very much for your help!
    Wednesday, September 10, 2014 12:56 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    What we do is to add an intermediate step to capture the details of file along with data it loaded (rows added) to a control table. We do comparison whether any data was loaded before we actually remove the file. We also dont delete file as is but will move it to a different archive folder. This will give us an opportunity to get back file and load it incase initial load didnt happen due to some reason. The files are deleted in bulk after an ageing period (say 2 weeks)

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Wednesday, September 10, 2014 1:42 PM