I'm having an issue with an SSIS package that's intended to handle Online Student's Course Add/Drops. The meat and bones of the package is the section where a student's request is located, parsed, fit into parameters, thrown through a cursor, and SQL Agent sends out via email to parents. The email holds the details of a student's Added or Dropped courses (via a table), as well as a blurb regarding all relevant details.
THE PROBLEM IS THIS (and very recent):
Students & Parents are receiving more courses than they've selected. The courses that are appearing which students didn't select do appear within the table being referenced to pick out student data, but the bad courses belong to different student requests. Again, this isn't something that happens consistently... in fact it's only started occurring within the last month. I originally associated it to an issue where requests were not sending for about 30 days, so when we finally uncorked the bottle (so to speak), a deluge of requests came through and it confused the cursor..... which was a shaky explanation at best....
Well, now it's happening again and I still have no good reason. I've attached the cursor script outlining the nuts & bolts, as well as an example of a problematic email. The picture shows the email the student's parents received as a result of the student's request processing through the cursor. The problem being that this student had only requested to add OL Spanish 3 but got WAY more than that one class. Other students within the table the cursor used had requested the extra courses, but this student did not.
If you have ANY idea why I'm getting this cursor record bleed-over, I'm stumped. Please help
November 10, 2022 at 9:50 pm
I don't see the cursor code. Maybe paste it into a code block.
What is there in the business logic that requires a cursor? Does the cursor load the table that is used to generate emails? and have you confirmed that the table does indeed have this many course requests assigned to the student? What role does SSIS play? is the student request data in a format that is tricky to parse?
November 11, 2022 at 3:26 pm
The forum keeps kicking my code blocks out as SPAM or not allowing me to attach SQL or TXT files. I'm sorry for the jpgs, but this is the best I can do until I understand this forum a little better (it's my first day... in forum). In response to the other questions:
What is there in the business logic that requires a cursor?
The district employs 2 google forms for students to add/drop courses for our newly established Online School. Those forms feed to 2 google sheets, and we grab the values from those sheets and shove them into CSVs. The SSIS parses, ingests, and applies our Student Information System data to the records from those CSVs (Online Course Add & Drop Requests respectively). The cursor works in place of a while loop to use the table holding the Add & Drop results to populate an email (see screen shots) that we send to students and guardians. This email is to request Guardian permission for the request to be finalized. There's a bunch of stuff that happens afterwards, but hopefully this is enough to explain the "Why"
Does the cursor load the table that is used to generate emails?
Yes.
Have you confirmed that the table does indeed have this many course requests assigned to the student?
I have affirmed that it does and it doesn't. The xlsx file I attached shows one kid in particular. Pass 1 shows the results from his 11/3 request, while Pass 9 shows the results from his 11/10 request. The 11/10 request ended up as the 9th pass within the 14 students making requests within this batch through the cursor. 11/3 is correct, but 11/10 has A LOT more courses than he should.
What role does SSIS play? is the student request data in a format that is tricky to parse?
The SSIS handles the CSV population from the google sheets, the centralizing of the CSV data, the firing of the email proc, and the population of data within a neutral server so non-detail data can be accessed outside our network. God I hope all of this doesn't get kicked out as SPAM
With a little help from a new friend (and far more help than I deserved), I was able to figure out the issue. The @Local table wasn't wiping out between Fetches... nor was it giving an error so it may have been a bug...
Regardless, I killed off the table and moved the SPLIT_STRING function to the Dynamic section. This fix resolved everything (all credit to John R.). Thanks for responding Ed B. I was just introduced to this site as I work in a district that has no other data resources. Without anyone to bounce ideas off of, I'm flying blind almost daily. I've learned a lot with my feet pressed to the fire constantly but it doesn't always result in the most elegant solutions.
+
CAST((SELECT DISTINCT
td = l.value
FROM STRING_SPLIT(@Courses, ',') l
ORDER BY l.value ASC
FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))
+
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply