spacelord


We are storing incoming flatfiles into a text field in a table and then we are processing this table on a regular basis. What I would like to do is to get this flatfile from the textfield and populate a flatfile source with it, but so far I have only been able to do that with XML files as there are no option for doing that with the flatfile source. Using the disk as a temporary storage for the flatfile is prohibited.

Does anyone have any suggestions on how to solve this




Re: Populate flatfile source from variable

MatthewRoche


I generally solve this problem by "shredding" the flat file before storing its data in a relational staging database, not trying to store a flat file as a single BLOB, which sounds like what you're trying to do. Working under the assumption that you're going to be putting the data from the flat files into a relational database at some point, there should be little drawback to doing it here, and from that point on you can work with the more structured data.

Will this solve your problem There is no way that I am aware of to point a flat file source component at a column in a database table, (which is what it sounds like you really want to do) so following down your original direction will likely involve some custom development.







Re: Populate flatfile source from variable

jwelch

Another option would be to use an OLE DB source, bring in the column as TEXT, then shred it in the dataflow

For some examples of doing this, see:

http://search.live.com/results.aspx q=recordset&q1=macro:jamiet.ssis&first=1

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx







Re: Populate flatfile source from variable

Phil Brammer

Are you saying that you are storing the flat file name/path in a table that you process regularly If so, this is easy.

In the control flow, execute a SQL task to select the records you need (file name/path). Store the results in an object variable. Then use a foreach loop to "shred" that variable into unique iterations. Add a data flow task to the foreach loop. From there, you can assign the flat file source connection to the foreach variable which contains a file name for each iteration.

Phil





Re: Populate flatfile source from variable

Phil Brammer

If you are indeed storing the contents of the flat files in a single column (seems counter-intuitive as you're still using disk), then the other suggestions above seem to be a good fit.