none
SSIS File System Task Help

    Question

  • Hi folks,

    I have a table called Attachment and it looks like this:

    ID File_Name File_Details
    1 abc.xlsx abc_20121121_150142.xlsx
    2 xyz.pdf abc_20121121_150142.pdf
    3 123.xlsx 123_20121121_150142.xlsx

    The column File_Name contains the name of the files stored in a source file path. And File_Details stores the same File Name in the format <name>_YYYYMMDD_HHMISS.<extn>. I have an Execute SQL Task that will store the Full ResultSet from the Attachment  table in an object variable. After that, the package should check in the source file path for the files returned from the Exceute SQL Task.

    For e.g.: if in the source file path, if there are files with the same name as given in the File_Name column, then it should move all those files to a destination file path. In the destination path, the file names should be having the name as given in File_Details column.

    i.e if abc.xlsx, xyz.pdf, 123.xlsx are there in the source file path, they should be moved to a destination file path with the names abc_20121121_150142.xlsx, abc_20121121_150142.pdf, 123_20121121_150142.xlsx respectively.

    The approach I have taken is explained below:

    1. Execute SQL Task to get the File_Name and File_Details and store in an object variable called varFile

    2. A For Each Loop Container (having Foreach ADO Enumerator)

    3. A File System Task inside the Foreach Loop Container.

    Can I please get some help from you to complete my task?

    Thanks

    Sharath


    Sharath



    • Edited by Sharath048 Wednesday, November 21, 2012 1:23 PM
    Wednesday, November 21, 2012 1:21 PM

Answers

  • Perhaps you can simply use the Conditional Split component to conditionally direct the data flow to one side or another.

    The SSIS expression that would drive the split could the FINDSTRING function based where you search for the 1st occurrence of one string in another, of the result is positive then it branches to once side, otherwise another in the very ForEach Loop.


    Arthur My Blog

    Wednesday, November 21, 2012 6:42 PM
    Moderator

All replies

  • In expressions of precendence contraint, you would have to substring based on the logic you will recongonize that particular file, and then manage accordingly

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

    Wednesday, November 21, 2012 1:57 PM
  • Perhaps you can simply use the Conditional Split component to conditionally direct the data flow to one side or another.

    The SSIS expression that would drive the split could the FINDSTRING function based where you search for the 1st occurrence of one string in another, of the result is positive then it branches to once side, otherwise another in the very ForEach Loop.


    Arthur My Blog

    Wednesday, November 21, 2012 6:42 PM
    Moderator
  • Thanks for your replies. I got it working :)

    Cheers!!

    Sharath


    Sharath

    Thursday, November 22, 2012 6:38 AM