Here's the scenario:

I've got two tables: school and student:

create table school (
school_id int,
name varchar(25)
)

create table student (
student_id int,
firstname varchar(25),
lastname varchar(25),
etc...
)

I have a stored procedure that returns student data based on a
school_id parameter (student and school are joined to other tables to
create the relation, but that's not important here).

create procedure get_students_by_school
@school_id int

as

select firstname, lastname...from student join ... join school on ...
where school_id = @school_id

....

I want to create a DTS package that executes the stored procedure for
each school_id and stores the results in a different CSV file
destination for each school.

Questions:

1) How do I execute a stored procedured, passing in a parameter from a
data-driven list?
2) How would I put those results into a dynamically created destination
file?