Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

ssis for each loop into files help Expand / Collapse
Author
Message
Posted Sunday, December 15, 2013 2:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:06 PM
Points: 102, Visits: 582
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
Post #1523066
Posted Sunday, December 15, 2013 10:32 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:47 AM
Points: 4,973, Visits: 11,662
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1523083
Posted Monday, December 16, 2013 1:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:06 PM
Points: 102, Visits: 582
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.
Post #1523114
Posted Monday, December 16, 2013 2:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:47 AM
Points: 4,973, Visits: 11,662
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



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1523124
Posted Monday, December 16, 2013 2:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:06 PM
Points: 102, Visits: 582
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?
Post #1523127
Posted Monday, December 16, 2013 3:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:06 PM
Points: 102, Visits: 582
I've worked it out now, so I don't need help on how to do this.
Post #1523135
Posted Monday, December 16, 2013 3:21 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:47 AM
Points: 4,973, Visits: 11,662
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1523137
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse