Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamically Generate Folders for File Output using Variables in SSIS Expand / Collapse
Author
Message
Posted Monday, June 16, 2008 8:18 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,221, Visits: 2,614
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
Post #517976
Posted Monday, June 16, 2008 11:30 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, November 3, 2010 2:32 AM
Points: 1,249, 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
Post #518009
Posted Tuesday, June 17, 2008 8:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, March 4, 2011 1:09 AM
Points: 56, 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)
Post #518307
Posted Tuesday, June 17, 2008 9:54 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, August 14, 2014 12:26 AM
Points: 5,335, Visits: 1,383
Nice article.....


Post #518399
Posted Tuesday, June 17, 2008 7:01 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,221, Visits: 2,614
You have to open Sql Server Business Intilligence Development Studio.

Ken Simmons
http://twitter.com/KenSimmons
Post #518673
Posted Thursday, June 19, 2008 6:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 4:04 AM
Points: 1,865, Visits: 591
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
Post #519770
Posted Thursday, June 19, 2008 7:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 28, 2009 9:41 AM
Points: 5, 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.

Post #519826
Posted Thursday, June 19, 2008 4:59 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
Excellent article. Thank you. This may prove useful.



---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #520284
Posted Friday, June 20, 2008 6:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 28, 2009 9:41 AM
Points: 5, 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!
Post #520618
Posted Tuesday, October 7, 2008 3:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 20, 2014 9:15 PM
Points: 27, Visits: 190
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.
Post #581612
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse