Passing parameters to an OLE DB Command transformation (Using a CTE)

I must start this post by saying that I am not a big fan of using OLE DB Command. While this transformation may look appealing to some common ETL scenarios; it really slows down the overall performance as that command gets executed for every row that goes through the data flow pipeline. Anyway, I have been playing with it and realized that its SQL parser is just 'picky' and actually has problems when mapping parameters in half way complicated querys (Just as the OLE DB Source component).

My test used the AdventureWorks DB and consisted of a OLE DB Source component that retrieves 3 EmployeeID from Employee:

SELECT e.EmployeeID FROM HumanResources.Employee AS e with (NoLock)WHere e.EmployeeID in (268,284, 288)

Then I wanted to use an OLE DB Command to update (times 2)the VacationHours of every employee that reports directly or indirectly to the 3 employees in the previous query.

For that, I wanted to use an Update statement based on a CTE (Common Table Expressions):
SET NOCOUNT ONBEGINWITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)AS(SELECT e.EmployeeID, e.VacationHours, 1FROM HumanResources.Employee AS eWHERE e.ManagerID = ?UNION ALLSELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1FROM HumanResources.Employee as eJOIN DirectReports AS d ON e.ManagerID = d.EmployeeID)UPDATE HumanResources.EmployeeSET VacationHours = VacationHours * 2FROM HumanResources.Employee AS eJOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;END

As you see, the question mark would represent the parameter to be mapped to the EmployeeID in the first query; but when you paste the update Statement inside of the OLE DB Command, it will throw an error:

[OLE DB Command [34]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Syntax error, permission violation, or other nonspecific error".

Well, the only work around I could find was to enclose the update statement inside of a stored procedure and then just call that SP from the OLE DB Command.

This is the code of the SP:
CREATE procedure [dbo].[UpdateUsingCTE]@ipManagerID INTASSET NOCOUNT ONBEGINWITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)AS(SELECT e.EmployeeID, e.VacationHours, 1FROM HumanResources.Employee AS eWHERE e.ManagerID = @ipManagerIDUNION ALLSELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1FROM HumanResources.Employee as eJOIN DirectReports AS d ON e.ManagerID = d.EmployeeID)UPDATE HumanResources.EmployeeSET VacationHours = VacationHours * 2FROM HumanResources.Employee AS eJOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;END

And this is how it gets called from the OLE DB Command transformation:

Exec UpdateUsingCTE ?

After doing that, my package run without problem and all expected rows were updated correctly.

You may find putting the SQL statement inside of the SP convenient for other reasons like maintenance (for example, if that statement is used multiple times)