none
SSIS moving files to different folders dynamically

    Question

  • Hi all,

    Can you help me in moving files in separate folders.

    I have a folder " Source" in which i have few text files (1.txt, 2.txt , 3.txt etc) I have to move them into separate folders (not in 1 folder) like folder 1 which will have 1.txt, folder2 which will have 3.txt, folder3 which will have 3.txt. and so on.

    Then I have to zip those folders.

    If you know somethng please let me know.

    Thanks

    Ankur

    Saturday, July 16, 2011 8:33 PM

All replies

  • If you loop through the folder with a Foreach Loop, then you have the filepath in a SSIS variable. You should create an other string variable and add an expression (via the properties) to it, to compose the path of the destination folder (Also set EvaluateAsExpression to True). Then add a FileSystemTask to create the folder and a FileSystemTask to move the file to that folder. The expression could look something like this:
    "D:\\archive\\folder" +
    SUBSTRING(@[User::FilePath],
    LEN(@[User::FilePath]) - FINDSTRING(REVERSE(@[User::FilePath]),"\\",1) + 2,
    FINDSTRING(REVERSE(@[User::FilePath]),"\\",1) -5)
    There is still room for improvement at the expression. Let me know if it worked.

    http://microsoft-ssis.blogspot.com/2011/01/zip-sourcefile-to-archive.html?showComment=1310846086085#c145159857859698449 


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com



    Saturday, July 16, 2011 8:58 PM
  • Can you help me in moving files in separate folders.

    I have a folder " Source" in which i have few text files (1.txt, 2.txt , 3.txt etc) I have to move them into separate folders (not in 1 folder) like folder 1 which will have 1.txt, folder2 which will have 3.txt, folder3 which will have 3.txt. and so on.

    Then I have to zip those folders.

    If you know somethng please let me know.

    Thanks

    Ankur


    as
    Saturday, July 16, 2011 8:58 PM
  • Hi Apna,

    You can use a for each file enumerator, to loop around each file in the original folder. You can then have a lookup table in a database, where you can choose the folder name based on the file name. You can then pass the approriate folder name into a variable and use that in an expression on a file system task, which is set to move the files. Then, you can use an execute process task, which will run command line util, such as gzip or winzip (there are also custom SSIS tasks available on the net) which zips the folders. Again, use an expression, with the folder name you have retrieved from the lookup table.

    Hope this helps.

    Pete


    Peter Carter http://sqlserverdownanddirty.blogspot.com/
    Saturday, July 16, 2011 9:00 PM
  • Hi Apna,

    You can have a for each file enumerator, which loops around each file in the source folder, gets the name, then does a lookup to a control table, to retrieve the appropriate folder name. Then pass the folder name into a variable, and use this variable in an expression on a file system task that is configured as a move file.

    You can then pass the folder name into an expression on an execute process task, that calls a command line util, such as gzip or winzip, to zip the folder. There are also a number of custom SSIS components on the market that will do this.

    Hope this helps.


    Peter Carter http://sqlserverdownanddirty.blogspot.com/
    Saturday, July 16, 2011 9:04 PM
  • Saturday, July 16, 2011 9:13 PM
  •  

    Thanks for help.

    But its not working for me.I think I am doing mistake in configuring the variables.

    Can you tell me step by step if you can.So that I can check where I am lacking.

    -- Ankur

     

     


    as
    Saturday, July 16, 2011 9:14 PM
  • The FilePath variable is just for the Foreach Loop. Thats where the fullpath of the file is stored: d:\sourcefolder\1.txt

    The DestinationFolder variable has an expression in it to compose the target folder: d:\archive\folder1\

    Now you can use that second variable to create a folder (first task) and you can use it to move the file to (second task).

     

    Download SSIS 2008 package

     


    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
    Saturday, July 16, 2011 9:19 PM
  • Hi Joost

    I can move only 1 file in a folder.

    I give you my whole scenario. I have a folder "Source"  path : {C:\Users\ANKUR\Desktop\Source}

    It has 4 files (62.txt, 63.txt, 123.txt, 124.txt) [for your concern these text files coming from a table based on each buyer. 62,63,123,124 are buyers ID and each text file contains each buyers related data in it and these files will come on daily bases so file name will be different each time]

    Now I have to move them in separate folders and after that I have to zip them.

    I hope now you can understand what I want to do.

    Please help me.

    Ankur

     


    as
    Saturday, July 16, 2011 9:52 PM
  • Why dont you use a Script task inside the For each loop, assing the folder names dynamically inside, create folders

     

    I havent tested the code but this should help

     


        DIM strDestStringFolder, strDestStringFullpath, strSourceFilePath
        DIM FS as FileStream
        Dim objStreamReader As StreamReader, objStreamWriter As StreamWriter
        Dim strLine As StringBuilder
        objStreamReader = New StreamReader(strSourceFilePath)
    Do While Not strLine Is Nothing

          'Write the line to the Console window.
          Console.WriteLine(strLine)

          'Read the next line.
          strLine.Append(objStreamReader.ReadLine.ToString())
        Loop

        'Close the file.
        objStreamReader.Close()


       
        'strDestStringFolder = <Assing the Destination Directory>
        strDestStringFullpath = strDestStringFolder + <FolderNameuWant> + <Filename>
            fs = new FileStream(strDestStringFullpath , FileMode.Create)
        objStreamWriter = new StreamWriter(fs
        objStreamWriter.WriteLine(strLine)

           'Close the file.
           objStreamWriter.Close()
        strline.dispose()


    For zipping the directories below should help

     

    http://forums.digitalpoint.com/showthread.php?t=674969

     


    Abhinav

    Sunday, July 17, 2011 12:13 PM
  • Thanks Joost

    your Links helped a lot.

    Do you have any idea about zipping individual folders . I have to zip folders dynamically on daily based . all folders are in one directory.

    They will come on regular basis. and I have to create a package to zip them with the same name.

    Actually folders are populated from Customer ID. like (Customer1 folder, customer2 folder, customer3 folder etc)

    i have to zip them (customer1.zip, customer2.zip, customer3.zip)

    If you know something please let me know.

    Ankur


    as
    Sunday, July 17, 2011 11:51 PM
  • Create an SSIS package by following the below steps:
     
    1. Create 4 variables with the said properties as follows:
     
    Variable Name: SourceFolder, Data Type: String, Value: (Set the path of source folder where all the .txt files are present as the value of this variable); Purpose of this variable is to store the path of source folder where all the .txt files to be zipped are present which will be iterated through one by one using ForEachLoop Container in the package.
     
    Variable Name: DestinationFolder, Data Type: String, Value: (Set the path of destination folder as the value of this variable); Purpose of this variable is to store the destintion parent folder where you want to place all the zip files.
     
    Variable Name: File_Ext_Name, Data Type: String, Value: Enter a dummy value (eg. dummy.txt); Purpose of this variable is to store the file + extension name of the source files to be zipped. This will be set/overwritten using ForEachLoop Container in the package.
     
    Variable Name: FileFullyQualifiedName, EvaluateAsExpression: True, Data Type: String, Value: @[User::SourceFolder]+"\\"+ @[User::File_Ext_Name]; Purpose of this variable is to store the full path with filename and extension of source text files to be zipped. The value of this variable will be dynamially set based on SourceFolder and File_Ext_Name variables.
     
    2. After all the variables have been created, use a ForEachLoop Container with the following settings to iterate through all the files in source folder:
     
    Set the Directory property to @[User::SourceFolder]. (Note that this property cannot be set using the expressions tab. You need to set it through expessions option availale via the Collection tab of ForEachLoop Editor.)
     
    In Collection tab, under "Retrieve file name" select "Name and extension".
     
    Store the filename+extension retrieved by ForEachLoop Container in File_Ext_Name variable. To do this, go to Variable Mappings tab and select User::File_Ext_Name variable with Index = 0.
     
    3. Drag ExecuteProcess task within the ForEachLoop Container and set it to zip files and output it into the destination as follows:
     
    In process tab in Execute Process Task Editor, set "Executable" to path of command line util, such as winrar. For eg (C:\Program Files\WinRAR\WinRAR.exe)
     
    In expressions tab in Execute Process Task Editor, set the following properties:
     
    Working Directory: @[User::DestinationFolder]. (This will be the folder where the zipped files will be placed.)
     
    Arguments: "a -ep1 " + REVERSE(RIGHT(REVERSE(@[User::File_Ext_Name]), (LEN( @[User::File_Ext_Name] ) - FINDSTRING(REVERSE(@[User::File_Ext_Name] ) ,".",1)))) + ".zip " + @[User::FileFullyQualifiedName]
     
    The Arguments property tell the command line util to zip the file (without the base folders) with the name of zip folder and the name of source file to be zipped:
     
         a - swith to archive
     
         -ep1 - switch to leave base folders while zipping
     
         REVERSE(RIGHT(REVERSE(@[User::File_Ext_Name]), (LEN( @[User::File_Ext_Name] ) - FINDSTRING(REVERSE(@[User::File_Ext_Name] ) ,".",1)))) + ".zip - Name of zip folder
     
         @[User::FileFullyQualifiedName] - Source file to be zipped

    Cheers,
     
    Atif


    Intellisense

    Wednesday, August 15, 2012 8:07 AM