Almost two years ago, I wrote about a method to use Table-Valued Parameters in SQL 2005 – or basically any environment that doesn’t support them natively.
The idea was to use a View with an ‘instead of’ trigger. Essentially, the trigger acts as a stored procedure, which is then used to be able to handle all the rows however you want. That could be distributing the rows into tables as if it’s an actual insert, but also works to just run whatever code you like, as a trigger is essentially just a stored procedure.
So anyway – today I got challenged to make it more explicit that this also works within SQL Server Integration Services. SSIS handles batch inputs into views, but if you were hoping to push data into a stored procedure in a data flow, that’s going to be on a row-by-row basis – no TVPs. I’d described it to a class I was teaching, showed them the code from my 2011 blog post, and they pointed out that I hadn’t made it particularly obvious (in my blog post) that you could use that method in SSIS.
The idea is very simple – you just create a view, as I did in my earlier post, and then use it as a destination within SSIS. This can be used for all kinds of methods for which you’d want to use a TVP, including pushing data up into SQL Azure databases.
This Post Has 2 Comments
Hi Rob,
great idea, but it’s important to note that by default, this doesn’t work in SSIS because the default "Data access mode" option in an "OLE DB Destination" is "Table or view – fast load", which disable the triggers execution
To solve it, we can add "FIRE_TRIGGERS" option on "FastLoadOptions" in the advanced editor, or selecting "Table or view" on the "Data access mode" option
Yes – you do need to make sure that you’re putting the data in using a method that will cause the triggers to fire.