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 12»»

Creating Dynamic Outputs in SSIS Expand / Collapse
Author
Message
Posted Wednesday, September 23, 2009 11:02 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 5:32 PM
Points: 1,045, Visits: 2,722
Comments posted to this topic are about the item Creating Dynamic Outputs in SSIS



Tim Mitchell, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #793058
Posted Thursday, September 24, 2009 9:10 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, September 19, 2012 8:39 AM
Points: 595, Visits: 1,226
Nice article.

I try to use the ForEach container as much as I can. Saves lots of work.


Converting oxygen into carbon dioxide, since 1955.

Post #793358
Posted Thursday, September 24, 2009 2:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:40 AM
Points: 1, Visits: 186
Hi Tim,

wonderful article. However, when setting the vendor variable in the result set tab be sure to rename the property name (result name) to 0.

http://www.techsolutions55.com/microsoft/498b01c802393.php.

Thanks.

Francisco.

Post #793565
Posted Thursday, September 24, 2009 3:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 2, 2014 9:35 PM
Points: 8, Visits: 452
That worked for me. I was wondering what I did wrong. Thanks Francisco.
Post #793600
Posted Tuesday, September 29, 2009 12:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 30, 2011 9:01 AM
Points: 18, Visits: 6
Nice article! I was wondering if I will be able to use configuration file for dynamically naming the output file. I have used dynamic out put file in some of my packages. But I could not use a configuration file for my settings while using file name dynamically. May be I am doing something wrong.
Post #795371
Posted Tuesday, September 29, 2009 12:55 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 5:32 PM
Points: 1,045, Visits: 2,722
Jananth, you can use a configuration file to set the filename, but when used in the context of this example, the filename declared in the expression would win out at runtime. Are you trying to define the output folder (rather than the filename) in the config file? If that's the case, you could set a variable to define the output folder in the config file, and append your dynamic filename to that folder name.

Let me know if this answers your question.

hth,
Tim




Tim Mitchell, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #795395
Posted Tuesday, September 29, 2009 1:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 30, 2011 9:01 AM
Points: 18, Visits: 6
I am trying to use the file name itself in the config file.
Post #795416
Posted Tuesday, September 29, 2009 3:09 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 5:32 PM
Points: 1,045, Visits: 2,722
Sure, you can hard-code the filename in the config file if you want to use a single output file (rather than one file per iteration of the loop). Be aware that if you use repeating logic such as a ForEach loop, you'll need to set the flat file output to append rather than overwrite, otherwise you'll only get the data from the last iteration of the loop.

hth,
Tim




Tim Mitchell, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #795461
Posted Sunday, October 11, 2009 9:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 4, 2014 2:02 PM
Points: 11, Visits: 142
Hello,
Really nice article. I am newbie in this area.
Can I implement the same in my environment? here is the scenario.

1. I have lot of different tables data to be extracted to flat files like table1, table2, table3.....tableXXX.
2. I need to run select * from each table and capturing the data in flat files by naming the file as table name.
I am trying to use foreach loop in the case but getting error...I captured table names (table1, table2 etc.,) and Query (select * from table1, select * from table2 etc., ) to a temp table and then using the variables tablename and query as variables in the foreach loop container and ending with error.

So my question is ...can I use yours in my scenario? I heard some where that each table contains different columns and the each query leads to different columns and hence the columns are changing flatfile extraction is not possible. Is that correct?
Can you kindly suggest some method to my schenario?
Krishna.



Post #801320
Posted Thursday, October 22, 2009 8:54 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 5:32 PM
Points: 1,045, Visits: 2,722
Krishna,

Unfortunately, the most difficult scenarios to address are those with variable metadata. I understand that there are some custom components available for purchase that allow you to declare metadata outside the package (I think CozyRoc has something like this) but I've never used their tools so I can't speak to what they can or can't do.

An alternative could be to use a fully scripted solution (most likely a single Script Task) to read the file, create the table, and write the rows to the output.

hth,
Tim





Tim Mitchell, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #807569
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse