OLE DB Command: executing SQL statements on each row in the data stream

In the Data Flow Task there are times when there is a need to run an SQL statement. This statement may just do something and not return any result, or may get input values and generate output values.

The OLE DB Command provides a way to execute SQL statements on OLE DB connections to databases. We will try to implement the Update part of the previous recipe's Upsert scenario in this recipe.

Getting ready

The previous recipe should be executed before beginning this recipe.

Execute the following command on the PacktPub_SSISbook database to create the stored procedure for updates in the Person table:

Create PROCEDURE dbo.UpdatePerson 
   @ID nvarchar(10),
   @FirstName nvarchar(50),
   @LastName nvarchar(50),
   @Email nvarchar(50),
   @Url nvarchar(50)
AS
BEGIN
   UPDATE [dbo].[SalesPerson]
      SET [FirstName] = @FirstName
        ,[LastName] = @LastName
        ,[Email] = @Email
        ,[Url] = @Url
    WHERE [ID]=@ID ...