SSIS Execute SQL Task package works in BIDS but fails when Deployed in SQL Agent

I have a SSIS package with an execute SQL task. Here is the code

declare @vPersonSourceTgt int, @CntFlag int

set @vPersonSourceTgt =  (select count(*) from tbl_ONL_Person)

set @CntFlag = case when @vPersonSourceTgt = ? then  1 else  0 end

select @CntFlag as vTargetCntFlag

i have made sure that the input parameter name is 0 in the parameter mapping. Everything works fine in BIDS, but when i deploy the package in SQL Agent. It fails with the following error.

Description: Executing the query "declare @vPersonSourceTgt int, @CntFlag int    set @vPersonSourceTgt =  (select count(*) from tbl_ONL_PALSAccountPerson)    set @CntFlag = case when @vPersonSourceTgt = ? then  1 else  0 end    select @CntFlag as vTargetCntFlag开发者_开发问答" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).

Any help would be highly appreciated. Thanks!

The most common reason for packages failing when run as jobs is permissions: in BIDS the package is running with your account's permissions; when scheduled, it runs with the SQL Agent permissions. Are you using Windows authentication to connect to the database, for example?

Another common issue is that you have components on your workstation that are not installed on the server, e.g. third-party OLE DB providers. But that doesn't seem likely to be the case here, because the errors are usually more specific.

There is a KB article with general advice on how to troubleshoot and resolve problems with packages running as jobs.