October 30, 2012 at 10:54 am
I have sales data in a SQL server database.
The data has sales for specific groups e.g Marrows, Simpsons etc. I would like to automate the output of Sales data into individual excel files this using SSIS.
I would like so that when the package runs the data is exported into each individual excel file.
For instance Marrows_Oct_2012, Simpsons_Oct_2012.
Please could someone direct me and let me know which data flow transformation could solve this.
Many Thanks
October 30, 2012 at 11:36 am
It's not a data flow transformation.
Query the distinct values in the column, using an SQL Script task, assigning the resultant dataset to a variable, as usual.
Then step through that using a For Each loop, and use the value from the query as part of the connection string for the output file name. Use an Expression for the output path.
The data flow goes inside the For Each loop, and the value from the dataset row gets used in the Where clause of the query.
It'll step through the values and output one file per.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 30, 2012 at 11:43 am
Hi,
You can create a SSIS package using BIDS.
1. Create two data flow tasks
2. Data flow Task 1 will have OLE DB source, Data converter and excel destination(For groupA)
3. Data flow task 2 will again have OLE DB source, Data converter and excel destination(For groupB)
4. Connect task 1 to task 2 in Control flow
5. Save the package and through SQL Agent add this is a SSIS job.
This should do it.
Let me know if you need more detailed steps
October 30, 2012 at 12:33 pm
menon.satyen (10/30/2012)
Hi,You can create a SSIS package using BIDS.
1. Create two data flow tasks
2. Data flow Task 1 will have OLE DB source, Data converter and excel destination(For groupA)
3. Data flow task 2 will again have OLE DB source, Data converter and excel destination(For groupB)
4. Connect task 1 to task 2 in Control flow
5. Save the package and through SQL Agent add this is a SSIS job.
This should do it.
Let me know if you need more detailed steps
That works just fine for a very small number of files and if the number of files will never increase. A loop works better for any other situation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply