Dynamically Generate Folders for File Output using Variables in SSIS

  • KenSimmons

    SSCertifiable

    Points: 7822

    Comments posted to this topic are about the item Dynamically Generate Folders for File Output using Variables in SSIS

  • pduplessis-723389

    SSChampion

    Points: 10539

    Really nice article, with a lot of how-to's.

    I cannot stop raving about the added flexibility, such as what is described in the article, which will make SSIS king of all ETL tools for years to come.

    If you want to really get funky, add an ADO enumerator on top of this (for example, if you are processing an archive with 300 files, all with a datestamp attached, and you want to move them to monthly archive folders which dont exist, have a SQL task that strips out the month and adds distinct months into the ADO enumerator).

    This will allow you to add folders for the respective month, and a for each file enumerator inside the for each ADO enumerator, with a restrictive wildcard will allow you to only move those files belonging to the month you just created.

    Also, add some package configurations, which will allow you to port your solution to any environment.

    Key requirement is naturally that the SQL server agent account has write access to the folder.

    My 5 cents worth.

    ~PD

  • aysegul

    Right there with Babe

    Points: 718

    Hi,

    I can't do the example.

    How can i do? I couldn't find the window in the first picture ( IN sql 2005 management studio)

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice article.....

  • KenSimmons

    SSCertifiable

    Points: 7822

    You have to open Sql Server Business Intilligence Development Studio.

  • hodgy

    SSCertifiable

    Points: 5735

    Also, in the example in this article you can do away with the SQL statement and enter the expression directly into the variable.

    + Select the variable and go to it's properties.

    + Change the option "EvaluateAsExpression" to true, then click in the Expression option and open the expression builder.

    + Use this to build your variable's value.

    Just another way in SSIS to get things done.

    Life: it twists and turns like a twisty turny thing

  • dfalzone

    SSC Journeyman

    Points: 91

    wow, what great timing, thanks Ken!

    I have just been given an assignment to produce something exactly like this and earlier solutions I found seemed quite cumbersome.

  • TimothyAWiseman

    SSCrazy Eights

    Points: 8819

    Excellent article. Thank you. This may prove useful.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • dfalzone

    SSC Journeyman

    Points: 91

    found this article yesterday and had my on solution running and also passing the variables between packages by the end of the day!:hehe:

  • Lee Hemmings

    SSC-Addicted

    Points: 421

    Cheers for this.

    Guys could you help me with the next step in my SSIS package. Am using the first part to create my folders but i want to use the ftp task next so i can download the files in to that newly created directory. But am having trouble passing the variable to teh ftp task as teh remote path.

    Any help would be great.

  • prabhakar.sqlbi

    Newbie

    Points: 1

    KenSimmons - Monday, June 16, 2008 8:18 PM

    Comments posted to this topic are about the item Dynamically Generate Folders for File Output using Variables in SSIS

    Hi Ken, 
    The above post was really helpful but it only resolve my issue partly. In my scenario, I need to move the files to archive folder monthly and load the next month files to the new directory and so on...I have created a file system task to archive the files but how can i create a new archive folder and give that path to be loaded for next month in the package. Your solution is very helpful to pass it for one month. is there any solution that can help me iterate this process? Any thoughts with steps might be grateful.

  • Phil Parkin

    SSC Guru

    Points: 243779

    hodgy - Thursday, June 19, 2008 6:18 AM

    Also, in the example in this article you can do away with the SQL statement and enter the expression directly into the variable.+ Select the variable and go to it's properties. + Change the option "EvaluateAsExpression" to true, then click in the Expression option and open the expression builder. + Use this to build your variable's value.Just another way in SSIS to get things done.

    This is not just an alternative way of doing things, it is a better way than what is described, as it avoids a round trip to the SQL engine and avoids running any queries.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply