SSIS Flat File Export

Can someone point me in the right direction for building a flat file? I have to build a flat files made up of fields based on a source codes. I can have up to 10 different source codes and need to export the records from a table based on the source code. Ex: 1st flat file will be named sourcecode1, 2nd flat file sourcecode2, etc. Script is: Select * from table1 where sourcecode = 'sourcecode1' for first flat file and then Select * from table1 where sourcecode = 'sourcecode2', etc. Is there a way to make the table a variable and then have SSIS loop through all sourcecodes in the table building each flat file. I have the package set up now where the sourcecode is a variable but I have to change this variable each time as well as the name of the flat file. I would like an automated approach where it reads all sourcecodes and exports data for each flat file based on this criteria. Thanks!
more ▼

asked Oct 11, 2011 at 08:45 AM in Default

redder gravatar image

158 17 21 23

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

You'll need a for/each loop container with a variable for the current sourcecode. The loop will have the flatfile output which you'll need to configure with an expression for the filename so you can get the sourcecode into the file name.

Here is an almost exact step by step walk-through of what you're looking to do - [http://bi-polar23.blogspot.com/2008/02/dynamic-flat-file-destinations.html][1]

[1]: http://bi-polar23.blogspot.com/2008/02/dynamic-flat-file-destinations.html
more ▼

answered Oct 11, 2011 at 09:41 AM

KenJ gravatar image

20.3k 1 4 12

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 11, 2011 at 08:45 AM

Seen: 650 times

Last Updated: Oct 11, 2011 at 08:45 AM