Obscure Problems and Gotchas

Gotcha

This is a problem I have encountered a few times already when using SSIS with an OLE DB Source and a stored procedure. All the mappings are correct, but it just seems like SSIS doesn’t like it when you try to directly apply a “?” parameter directly to the stored procedure sometimes. I have seen this work just fine in some cases and have a problem in other situations.

Error example

[OLE-SRC <Table Name> Table [153]] Error: The SQL command requires a parameter named “@<Parameter Name>”, which is not found in the parameter mapping.

You get this pretty useless error. To make matters worse in my case all I was doing was taking existing SQL and moving it to a stored procedure.

What doesn’t work and will throw an error sometimes

EXEC [dbo].[SprocBeingUsedWithSsis]
      @param0 = ?
     ,@param1 = ?
     ,@param2 = ?
     ,@param3 = ?
     ,@param4 = ?

What fixes the problem

I hope this fixes it every time, but I don’t know if it does. So instead of using direct “?” parameter assignment you should first park the “?” in a variable (which is frustratingly redundant) and then assign the variable to the stored procedure. This has solved this problem for me twice already.

DECLARE
	@param0 DATETIME = ?,
	@param1 VARCHAR(255) = ?,
	@param2 BIT = ?,
	@param3 varchar(100) = ?,
	@param4 varchar(25) = ?
	
EXEC [dbo].[SprocBeingUsedWithSsis]
	 @param0 = @param0
	,@param1 = @param1
	,@param2 = @param2
	,@param3 = @param3
	,@param4 = @param4

Conclusion

Don’t let SSIS errors mislead you with their misleading wording. You have to step away from the situation sometimes and think above the immediate problem sometimes to see what got you into this position. SSIS is a tricky animal sometimes.