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 query help

Can anyone see why this only works if an address had no enddate? Something is going wrong in the WHERE Clause, if the enddate is null it works fine. What I need is to look at the enddate and if it is Null or >= today the record should be returned. I only want to use the Month and Day of the date.

Thank you for any help,

CREATE PROCEDURE [dbo].[sp_DataReaderName] @.SID int AS
SELECT
 CASE
 WHEN A.[CompanyName] IS NULL OR A.[CompanyName] = '' THEN C.[FirstName] +" "+ C.[LastName]
 ELSE A.[CompanyName]
 END AS DRName,
 C.Client_ID
FROM
 tblClients C
INNER JOIN
 tblClientAddresses A ON C.Client_ID = A.Client_ID
WHERE
 (C.Client_ID = @.SID) AND
 (A.MailTo=1) AND
 (A.EndDate Is Null OR (DatePart(m,A.Enddate) >= DatePart(m,GETDATE()) AND DatePart(d,A.Enddate) >= DatePart(d,GETDATE())))
GO

Its late and I've not spotted anything obvious except...DON'T prefix with "sp_" you'll incur a needless performance cost. SQL Server will think its a Microsoft Proc and go straight to Master.