ssis for each loop into files help

Question

ssis for each loop into files help

SQL_Kills

Hall of Fame

Points: 3154
More actions
December 15, 2013 at 2:47 pm

#280324

Hi,
I have the following table
CREATE TABLE [dbo].[Orders](
[orderNo] [varchar](10) NOT NULL,
[orderLineNo] [int] NOT NULL,
[product] [varchar](10) NULL,
[orderQty] [int] NULL,
[receivedQty] [int] NULL,
[orderStatus] [varchar](10) NULL
) ON [PRIMARY]
insert into values Orders values ('A1000',1,'ABC100',10,10)
insert into values Orders values ('A1000',2,'ABC100',15,15)
insert into values Orders values ('A1000',3,'ABC101',17,17)
insert into values Orders values ('A1001',1,'ABC101',5,4)
insert into values Orders values ('A1001',7,'ABC103',50,1)
insert into values Orders values ('A1001',8,'ABC104',25,25)
insert into values Orders values ('A1002',1,'ABC104',24,23)
insert into values Orders values ('A1002',2,'ABC100',15,15)
insert into values Orders values ('A1003',1,'ABC105',50,0)
For each OrderNo I want to create a Text File into my C: Drive Folder, so the file name will look something like this "C:\A1000.txt" with each text file having the result for each of the OrderNo's it loops through.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

Phil Parkin SSC Guru Points: 246686 More actions · Answer 1

For each OrderNo I want to create a Text File into my C: Drive Folder, so the file name will look something like this "C:\A1000.txt" with each text file having the result for each of the OrderNo's it loops through.

OK. Now please state your question.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 2

1. I need to create a ssis package

2. I want it to query the orders table for each orderid then for each result set it returns for each orderid to create a text file.

3. Each text file I would like the name of the orderid it has queried.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

SQL_Kills (12/16/2013)
1. I need to create a ssis package
2. I want it to query the orders table for each orderid then for each result set it returns for each orderid to create a text file.
3. Each text file I would like the name of the orderid it has queried.

I understand your requirement, but you have not told us which part you need help with. At the moment, I am therefore suspecting the following:

"Please write this entire package for me, for free, so that I can take all the credit and pretend that I know what I am doing."

--Edit: fix typo

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 4

Just advice on how to do this?

Would I need to first select a distinct list of Orderid's then pass this to the foreach loop container?

Not sure then how it would know which id it is going to use then when creating the file and if the results in the file relates to the same text file?

SQL_Kills Hall of Fame Points: 3154 More actions · Answer 5

I've worked it out now, so I don't need help on how to do this.

Phil Parkin SSC Guru Points: 246686 More actions · Answer 6

SQL_Kills (12/16/2013)
I've worked it out now, so I don't need help on how to do this.

Well done. I have some ideas about how I would do it and will post back later if I have time - maybe it will be of interest to you.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.