Creating Dynamic Outputs in SSIS

Question

Post reply

Creating Dynamic Outputs in SSIS

Tim Mitchell

SSCoach

Points: 15681
More actions
September 23, 2009 at 11:02 pm

#226480

Comments posted to this topic are about the item Creating Dynamic Outputs in SSIS
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

Viewing 15 posts - 1 through 14 (of 14 total)

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

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

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.

Francisco Hurtado SSC Rookie Points: 35 More actions · Answer 2

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.

tony-591909 SSC Enthusiast Points: 174 More actions · Answer 3

That worked for me. I was wondering what I did wrong. Thanks Francisco.

jananth SSC Journeyman Points: 76 More actions · Answer 4

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.

Tim Mitchell SSCoach Points: 15681 More actions · Answer 5

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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

jananth SSC Journeyman Points: 76 More actions · Answer 6

I am trying to use the file name itself in the config file.

Tim Mitchell SSCoach Points: 15681 More actions · Answer 7

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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

vvkp SSC Enthusiast Points: 124 More actions · Answer 8

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.

Tim Mitchell SSCoach Points: 15681 More actions · Answer 9

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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

dave-dj SSChampion Points: 11297 More actions · Answer 10

Tim - Thanks for posting.

I've managed to create a dynamic ETL process to split data into SQL tables (including creating the table if required) using this process with a few mods and it works like a charm! 😀

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Tim Mitchell SSCoach Points: 15681 More actions · Answer 11

Dave,

That might make a good article in itself 🙂

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

dave-dj SSChampion Points: 11297 More actions · Answer 12

Sure,

I've got no problem in creating an article for it. 🙂 Should be pretty quick to knock together.

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Thomas LeBlanc SSCrazy Eights Points: 8085 More actions · Answer 13

Thanks Tim. Followed this article step by step with no problem.

Thomas

Thomas LeBlanc, MVP Data Platform Consultant

aecusack Grasshopper Points: 13 More actions · Answer 14

Hi Tim,

Great article, your solution worked perfectly for me. I was wondering if you could point me in the right direction or expand on your article to enable dynamic Folder creations? I would like to learn more about being able to output a file and create a directory that relates to it:

EG:

\Site1\Site1.txt

\Site2\Site2.txt

Thanks for your time.