In most cases, stored procedures would be executed in the Control Flow with the IMAP Task. However, some processes may be more conveniently executed at the Data Flow level. You can execute stored procedures in a source component as well using the process outlined here.

Call Stored Procedures from a Source Component

Follow the steps below to execute a stored procedure with the SQL EXEC keyword:

1. Double-click the component in the Data Flow task to open the editor.
2. In the Data Access Mode menu, select SQL Command. The query syntax for stored procedure statements follows the standard form, shown below:
   

   EXECUTE my_proc @first = '1', @second = '2', @third = '3';
   
   EXEC my_proc @first = '1', @second = '2', @third = '3';

   EXECUTE and EXEC can be used interchangeably. See Using the Source Component for how to parameterize the query.