February 10, 2009 at 11:15 am
1. I need to populate a work file dbo.wk_RReef_GLEntries with one or more possible companies, this is done, no problem.
2. Now I need to create a seperate .csv file for each company in dbo.wk_RReef_GLEntries. Everytime this process runs could be a different combination of companies.
Sample data:
Company_srccd, additional fields......
00640, blah, blah, blah
00640, blah, blah, blah
00640, blah, blah, blah
00640, blah, blah, blah
00641, blah, blah, blah
00641, blah, blah, blah
Desired Output:
Dynamically create a table based on the distinct Company_srccd values in dbo.wk_RReef_GLEntries
00640_GLEntries_20090210.csv
00640, blah, blah, blah
00640, blah, blah, blah
00640, blah, blah, blah
00640, blah, blah, blah
00641_GLEntries_20090210.csv
00641, blah, blah, blah
00641, blah, blah, blah
Can someone provide some insight on how to do this.
Thanks,
Jeff
February 10, 2009 at 11:23 am
Create a query that selects the company IDs/names/whatever that you want files for. The idea is one row per file to be created.
Use that in SSIS to create a recordset and store it in an Object variable, then create a "For...Each...Next" loop that uses that recordset to run a query that pulls the data you want for the company. The loop can create a dynamically named file for the output and can run a Data Flow object that then populates the files with the data from the query.
The key is the initial query and the loop. Check it out, see if you can take it from here, ask questions if you can't. It's pretty straightforward, but I've been doing that for years and what seems straightforward to me might not actually be as simple as I think it is. 🙂
- 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
February 10, 2009 at 12:26 pm
Ok, I've loaded a Recordset with the distinct Company codes from my table.
Now I'm not sure how to configure the For..Each...Loop task to read the Recordset and dynamically create the .csv files.
Sorry,
Jeff
February 11, 2009 at 7:14 am
In BIDS, in your SSIS package, create an SQL object, have it run the query and assign the results to a variable of type "Object".
Then create a For...Each...Next loop in your package and assign it to the same variable.
- 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
February 11, 2009 at 4:03 pm
I was able to get this to work.
Thanks for your assistance.
February 12, 2009 at 7:21 am
Awesome. Glad I could help.
- 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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply