THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server) and leader of the SQL User Group in Adelaide, Australia. Rob is also a Director of PASS, and runs training courses around the world in SQL Server and BI topics.

TVPs in SSIS

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.

@rob_farley

Published Thursday, August 29, 2013 1:32 AM by Rob Farley
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

csm said:

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

September 4, 2013 4:32 AM
 

Rob Farley said:

Yes - you do need to make sure that you're putting the data in using a method that will cause the triggers to fire.

September 8, 2013 11:22 PM
 

Rob Farley said:

Using a hammer to push in a screw isn’t a good idea, no matter how good the hammer is. We all know that.

September 9, 2013 8:24 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement