October 19, 2011 at 4:11 pm
Hi all,
I just had a question posed to me and I thought I'd post it here for feeback, as i'm not much of an SSIS guy.
I have a query that returns information from a student records database, and one of the columns lists guardians (parents, grandparents, nannies, etc), which the query grabs from different places in the database.
The resultant column ends up being a list of guardians, separated by columns.
Guardians
--------------------------
Jack,Susan,Bob,Mary,Frank
This column is one of a number of columns that exist in a result set. What I need is a CSV file that contains the entire result set so I can export it to a student management product, but that has each guardian name listed as it's own separated value (basically guardian names are each in their own column in the CSV.
Any ideas of how to approach this?
First off, I'm not sure how to translate the original gaurdians column into separate columns in the csv, which is the biggest issue.
Second, I'm thinking that I will need to create a column immediately following the guardians column that contains some type of flag to let my student database import know that the list of guardian columns in the csv is done, as the number of guardians will vary from student to student.
Any feedback would be greatly appreciated.
Thanks
October 24, 2011 at 5:11 am
Take a look at the script-component transform in the data-flow. You can write code in the script-component to check the guardian column for multiple values and parse them out (if necessary).
The next question is what do you want to do with the parsed out values? Must they be broken out into separate columns or can you break them out into separate rows... Also, do you know the max number of guardians that can be specified in a comma-separated-list in the input-guardian field?
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply