Looping through a file

Hi,

Hopefully someone can assist me with what appears to be a simple issue - and hopefully I am just looking in the wrong location - because I am almost going nuts trying to work this out!!

I am wanting to pickup a file from a given folder, do some transformations on the data within the file, and then dump each row within the original file as a seperate txt file into a new folder.

I have managed to get it working - well all except having each row as a seperate txt file. Currently all the rows are outputed into the same txt file. argh

As it stands I have a For Each Loop Container, within this i have a Data Flow Container; which in itself, just has the source, some derived columns, and then an output.

How can I get this to pull each row from the source and put it as a seperate txt file. If someone can just nudge me in the right direction it would be much appreciated.

Thanks in advance

Troy

This might help:

Getting a value out of a file to use it in our package

(http://blogs.conchango.com/jamiethomson/archive/2005/06/15/SSIS_3A00_-Getting-a-value-out-of-a-file-to-use-it-in-our-package.aspx)

Its not exactly what you want to do but it uses the same concept (i.e. loop over the contents of a file and proces each record individually)

-Jamie

|||

Here's an example of using a script destination to output multiple destination files:

http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-output-destination-script-component.aspx

and here's one that doesn't use script.

http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-outputs-part-2.aspx

For your purposes, I would probably use the script based one, as it will be easier to modify.

|||

Hi Troy

I am also facing the same scenario.

Pls look into my form http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2085862&SiteID=17

But till date i didn't succeded.

Please update. Thanks

|||

Hi John/Jamie - thanks for the links; they were both pretty similar (considering the non-script one), so had a play and seemed to make sense.

Have run into another error now - when trying to make the file connection properties variable (error = External table is not in the expected format); but will post that in another post.

Antony - suggest you have a look at the posts above; they may be of assistance.

Thanks again

Troy

Looping through a file

Hi,

Hopefully someone can assist me with what appears to be a simple issue - and hopefully I am just looking in the wrong location - because I am almost going nuts trying to work this out!!

I am wanting to pickup a file from a given folder, do some transformations on the data within the file, and then dump each row within the original file as a seperate txt file into a new folder.

I have managed to get it working - well all except having each row as a seperate txt file. Currently all the rows are outputed into the same txt file. argh

As it stands I have a For Each Loop Container, within this i have a Data Flow Container; which in itself, just has the source, some derived columns, and then an output.

How can I get this to pull each row from the source and put it as a seperate txt file. If someone can just nudge me in the right direction it would be much appreciated.

Thanks in advance

Troy

This might help:

Getting a value out of a file to use it in our package

(http://blogs.conchango.com/jamiethomson/archive/2005/06/15/SSIS_3A00_-Getting-a-value-out-of-a-file-to-use-it-in-our-package.aspx)

Its not exactly what you want to do but it uses the same concept (i.e. loop over the contents of a file and proces each record individually)

-Jamie

|||

Here's an example of using a script destination to output multiple destination files:

http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-output-destination-script-component.aspx

and here's one that doesn't use script.

http://agilebi.com/cs/blogs/jwelch/archive/2007/06/03/multi-file-outputs-part-2.aspx

For your purposes, I would probably use the script based one, as it will be easier to modify.

|||

Hi Troy

I am also facing the same scenario.

Pls look into my form http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2085862&SiteID=17

But till date i didn't succeded.

Please update. Thanks

|||

Hi John/Jamie - thanks for the links; they were both pretty similar (considering the non-script one), so had a play and seemed to make sense.

Have run into another error now - when trying to make the file connection properties variable (error = External table is not in the expected format); but will post that in another post.

Antony - suggest you have a look at the posts above; they may be of assistance.

Thanks again

Troy

Looking for some advice.....

I have a couple of files that I have that are comma seperated, and am looking for the best way to take those files, but them in a temp location to run some sql up against, and than update or insert a production sql database based on a SP i have written that takes a number of variable. I have played around with using the recordset destination and defining all the variables and than using a for each loop but seem to be stuck. Somewhat new to the whole programming field, and to be honest, seems a little intimidating with the little I know of the programming side. Just looking for some thoughts.You could use a data flow to read the flat file, and use an OLEDB Destination to save it to a "temp" table. Use an Execute SQL to update the temp table, then use a second data flow to retrieve the data and send it to an OLE DB Command to call your stored procedure.