2

I have an excel spreadsheet with a column containing school names and other data - over 30 schools. I want to do the equivalent of filtering out all schools of 1 name into its own spreadsheet so I end up with over 30 separate files. Is this possible to do in SSIS only?

I expect to end up with as many spreadsheet as there are distinct schools in the chosen column.

I have managed to do it where I actually name an individual school ie [school]=="John Smeaton" but I cannot name all schools and will have to keep updating the script when new schools emerge or close.

Improve this question

1 Answer 1

Reset to default
6

Yes will do so using SSIS package. I am explaining it with the help of similar example: enter image description here In my case the source file is also a CSV file. Here I am trying to create a separate csv file for each Employee.

Steps to create separate csv file for each employee.

step 1.Create a data flow task which extract the records from CSV file and store them into a recordset Destination.

enter image description here enter image description here

step 2.Create a Foreach Container and configure the recordset(we create above) with foreach container and iterate accordingly.

enter image description here

Step 3.Create a User defined variable and store the employee number into it at every iteration. enter image description here

enter image description here

Step 4:Now create a data flow task inside the foreach container which reads the employee number from user defined variable and extract the employee details from the Source CSV file and and save the details into CSV file.

enter image description here

Here is conditional split Settings enter image description here Here is flat file Destination settings. enter image description here

Final package : enter image description here

Step 5: Execute the containerenter image description here

Output After executing the package:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Good answer. Maybe show him the settings of your conditional split and your flat file source in your last loop :)
Added the setting of conditional split and flat file source into the blog.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.