I used to be one of the counselors, or technically an "education camp intern"! We had a couple of campers who were out of towners and did the camp on vacation.
Yes, for the most part the parents would stay at a nearby hotel or vacation rental and drop off/pick up the kid. Of note, SW offered parents and other family members something like a $20 ticket to enter the park after the camp ended, so they could pick up the camper from the check in/out area and go back into the park. It could be a long day, but a lot of the kids were excited to show their parents and siblings what they just saw and learned and they would want to go back and do everything again. This kind of worked well when there was bad weather, because while we have a plan for what we do during the week, sometimes a thunderstorm would roll by and they would have to cancel a show or shut down a coaster that we just didn't have time to do again later in the week. I really don't remember what the exact price of those tickets were, or if they still offer them, but it's worth asking about it.
I believe the parents who were vacationing would use the time the kids where in camp to do more adult activities, like golf, spas, just sitting by the pool alone, etc. We once had two parents who were attending a convention at the Orange County Convention Center down the road, and camp worked out perfectly because they could drop their kid off while they did whatever work related things they need to do at the convention and pick them up when they were done.
Personally, I thought the camp was worth it and was something really special and unique for the kids. The full time education staff works really hard to make a solid agenda for the week, so we're not just aimlessly wandering around the park, like what honestly happened at a few other summer camps I worked at before SW. For the most part, every day we attended one show and went on one big ride, plus had lunch, snack, and arts and craft time. The younger kids also had a storytime, and the older kids (grade 6 and above) get to spend a day at Aquatica. Each camper also gets to the do the dolphin feed experience and gets to touch a (baby) shark and a penguin, plus feed the stingrays and seals and sea lions. Each camp group is lead by one full time SW education employee who knows almost everything about every animal. The camp takes the kids backstage in a lot of areas that I don't think even paid groups get to visit. We also got to speak with a lot of employees who would teach the kids what they do and about the animals they work with. I honestly haven't worked at other zoo or aquarium camps, but I would think SW would rank really high compared to a lot of them.
Feel free to message me if you have any questions!