Sql-server – Multiline UDF or Stored Procedure for SSIS Data Source

sql serverssisstored-procedures

We are creating an SSIS Data source with complex code. I read MultiLine Table Functions are slow. Would utilizing a stored procedure produce any faster results when streaming data from one place to another?

Table Valued Function Very Slow

Best Answer

It will always depend on the logic being implemented but I'd consider at least the following items when choosing between the two object types as a SSIS data source...

  1. Are you planning to modify data in a permanent table as part of the logic? You may be restricted to using a stored procedure if you need to perform interim DML when preparing the data source.
  2. Will your final result come from a SELECT from a temp table? It becomes more tedious to use a stored procedure as a data source as the temp table isn't created until sproc runtime; SSIS therefore can't see the table structure for column mappings in the data flow. Workarounds include using retaining connections at package level, utilizing global temp tables and delaying/bypassing validation of the package and affected data flows. It's a bit of headache compared to a table-valued UDF where you can define the output table structure up-front.
Related Question