Bulk insert with trigger execution with mssql package?

I receive a student enrollments report a couple of times a week in a XLSX file that I parse using Node. It's currently writing just fine to my own local MongoDB instance; I've been rewriting it to write to our main SQL Server 2012 instance. For this I have imported the mssql package and am currently connecting to a dev instance of SQL Server 2012.On the SQL server, I have an INSTEAD OF INSERT trigger on this dbo.enrollments table; instead of just a blind insert, it uses a MERGE statement to see if a student is a new insert, or if a student isn't included in the most recent report/source input, meaning they're no longer enrolled in that particular course, and deletes them from the table if not matched within the source input. If a source entry is matched in the table, then an UPDATE statement is run instead.The script currently connects to and writes to the dbo.enrollments table just fine on first run. The problem is that the INSTEAD OF INSERT trigger on the server isn't firing when the script is run, so any students not included in the most recent report are still available from the enrollments table. In addition, I get a primary key error on my next script run because the MERGE statement in the trigger isn't being called to handle duplicate entries with an UPDATE statement.I do know why this is happening - the bulk insert method in the mssql package uses bcp behind the scenes, and by default bcp doesn't fire triggers unless the FIRE_TRIGGERS hint is specified from the command line.My problem is I don't know how to prompt my sql object to use that FIRE_TRIGGERS hint, or if I'm able to do that at all. I've checked the documentation for any config options and nothing I've seen would do the trick. I've also rewritten the function to use a long "INSERT INTO... VALUES..." statement, which also failed because there's a hard 1000-rows limit when inserting data (this current report has over 4000 rows; even if I denormalized the table and consolidated student entries I would still have over 1200 rows). I've also gone directly into the package and tried tracing the function call to see if I could add it manually and honestly just got totally lost.Google and Stack Overflow have not been of any help to me. Has anyone here had experience with this? Is there a method through which I can use NodeJS to tell bcp to use triggers when running a bulk insert on a table? Should I be using another package? Is there a config option I've completely missed? Any pointers would be appreciated.

Submitted October 20, 2017 at 06:10AM by trueFleet