none
How can I get folder name ?

    Question

  • I have a folder name : Test under which I have many folders like : 20130131 , 20130228, 20130331 and so on.

    under which I have many .txt file like a.txt,b.txt .

    How can I get the folder name i.e 20130131, 20130228 , etc  in my package .


    Wednesday, October 30, 2013 6:43 PM

Answers

All replies

  • hi

    first you can use Execute SQL Task and run sql command 

    and you must create variable  in result set this variable.

    and now you have one  variable and data is 20130930 and you can create anther variable and you set FILE NAME+ variable  DATE +".txt"

     for file name .

    Wednesday, October 30, 2013 6:50 PM
  • When you presumably loop thru the parent folder with the "traverse" option set you get (if set) the full path captured into a package variable.

    Use it to extract the inner folder name via a SSIS Expression as

    FINDSTRING(foldername,"\",1) != 0 ? (SUBSTRING(foldername,1,FINDSTRING(foldername,"\",1) - 1)) : foldername


    Arthur My Blog

    Wednesday, October 30, 2013 6:52 PM
  • I dont want getdate() but I want the folder name . It can be 'abc', 'def', ghi' ...
    Wednesday, October 30, 2013 7:03 PM
  • Where is the GETDATE()?

    foldername 

    is your package variable name

    FINDSTRING([@User::FolderName],"\",1) != 0 ? etc.


    Arthur My Blog

    Wednesday, October 30, 2013 7:11 PM
  • If you have the complete filepath of a flatfile and you want to get the foldername, then you can either use a Script Task with some .net code: GetDirectoryName
    http://msdn.microsoft.com/de-de/library/system.io.path.getdirectoryname(v=VS.80).aspx

    Or you could use an expression to find the last and second last \ in the filepath and use those positions in a SUBSTRING:

    SUBSTRING(@[User::FilePath],
    LEN(@[User::FilePath]) - FINDSTRING(REVERSE(@[User::FilePath]),"\\",2) + 2,
    (LEN(@[User::FilePath]) - FINDSTRING(REVERSE(@[User::FilePath]),"\\",1)) - (LEN(@[User::FilePath]) - FINDSTRING(REVERSE(@[User::FilePath]),"\\",2)) - 1
    ) 
    
    or a little easier/shorter in SSIS 2012:
    TOKEN(@[User::FilePath],"\\",TOKENCOUNT(@[User::FilePath],"\\") - 1




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

    Wednesday, October 30, 2013 9:58 PM
  • I am getting it properly, please help me out.

    I have files in path : C:\example\test\abc\a.txt , C:\example\test\abc\b.txt ,C:\example\test\abc\c.txt and

                                     C:\example\test\xyz\a.txt , C:\example\test\xyz\b.txt ,C:\example\test\xyz\c.txt

    Variable - SourceDirectory : C:\example\Test\

    Now , Please explain me in details as I am new in SSIS. 

    How to get the foldername like  abc, xyz and so on. 

    As I have many folders in Test folder. I need to make it dynamic. I need the folder name as abc,xyz etc and also filename like a,b,c

    I need to look the a.txt,b.txt file for every folder.and need to store foldername and filename as well.

    Thanks in Advance.

    Thursday, October 31, 2013 7:47 AM
  • You can use one of the expressions from my previous reply:

    And for getting the filename from a filepath you can use an expression like:

    RIGHT(@[User::FilePath],FINDSTRING(REVERSE(@[User::FilePath]),"\\",1) - 1)


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


    Thursday, October 31, 2013 8:30 AM
  • Please elaborate .I am not getting you as I am new to SSIS.

    then How will it work for all the folders under my Test folder.?

    Thursday, October 31, 2013 9:08 AM
  • But this is for a definite path. But there are many folders inside Test folder.

    How can I make it dynamic?

    Thursday, October 31, 2013 9:21 AM
  • Hello,

    Its helped me when I am giving a definite path for a particular folder. 

    But had 20 folders under Test Folder. How Can I run it at once ?? Is it possible??

    Thursday, October 31, 2013 10:29 AM
  • You can use the Foreach Folder Container - File Enumerator to loop through all files.

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

    Thursday, October 31, 2013 2:01 PM