SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamically Generate Folders for File Output using Variables in SSIS


Dynamically Generate Folders for File Output using Variables in SSIS

Author
Message
KenSimmons
KenSimmons
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2548 Visits: 2614
Comments posted to this topic are about the item Dynamically Generate Folders for File Output using Variables in SSIS

Ken Simmons
http://twitter.com/KenSimmons
pduplessis-723389
pduplessis-723389
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3197 Visits: 400
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
aysegul
SSC-Enthusiastic
SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)SSC-Enthusiastic (180 reputation)

Group: General Forum Members
Points: 180 Visits: 101
Hi,
I can't do the example.
How can ı do? I couldn't find the window in the first picture ( IN sql 2005 management studio)
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9793 Visits: 1407
Nice article.....



KenSimmons
KenSimmons
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2548 Visits: 2614
You have to open Sql Server Business Intilligence Development Studio.

Ken Simmons
http://twitter.com/KenSimmons
hodgy
hodgy
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2645 Visits: 596
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
dfalzone
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 22
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
timothyawiseman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2384 Visits: 920
Excellent article. Thank you. This may prove useful.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
dfalzone
dfalzone
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 22
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
Lee Hemmings
Valued Member
Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)Valued Member (69 reputation)

Group: General Forum Members
Points: 69 Visits: 199
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search