Perhaps not everyone knows that it’s very easy to call stored procedures from a Data Flow and return values into the pipeline.
Just take this procedure:
CREATE PROCEDURE dbo.sp_test
@param1 int,
@param2 int,
@param3 int output
AS
BEGIN
select @param3=@param1+@param2
END
GO
Well, quite trivial, but it should work as an example…
So just imagine that you have two integer fields in your pipeline and want to have these added. The result should be in the pipeline for later use (i.e. to write it into a table). So what do you have to do?
Just add a field to the pipeline (i.e. use a Derived Column transform for that assigning a default value to it).
Then you can add an OLE DB transform to call the stored procedure. The command will look like this:
exec dbo.sp_test ?,?,? output
Then you simply have to do the column mapping for the three parameters. And that’s it…
I didn’t do any performance testing on that, but it would be quite interesting how good this performs… I’m quite curious if calling a stored procedure (perhaps even some C#/VB.net code) is faster than calling a VB.net script…
Thomas