CODEWITHSUNDEEP
ssis
Leah
Leah

Reputation: 1

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!

Upvotes: 0

Views: 2213

Answers (1)

Pondlife
Pondlife

Reputation: 16260

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.

Upvotes: 3

Related Questions