SSIS Package execution Issue

Question

SSIS Package execution Issue

Sushant Yadav

SSCrazy

Points: 2780
More actions
March 20, 2014 at 10:05 pm

#283653

Hi All,
I am Getting following error when I execute SSIS package through SQL agent job.
Executing the query "DECLARE @errorcode INT DECLARE @ErrorDesc Varchar(..." failed with the following error: "Must declare the scalar variable "@file_name".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Error: 2014-03-20 05:55:51.74 Code: 0xC002F210 Source: XXXXXXXXX Execute SQL Task Description: Executing the query "exec proc_PullActiveClients_exception_commodity" failed with the following error: "Column name or number of supplied values does not match table definition.". 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). Started: 5:30:09 AM Finished: 5:55:51 AM Elapsed: 1542.05 seconds. The package execution failed.
But when i execute the same from BIDS it executes fine.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27169 More actions · Answer 1

Check following link might help:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c2765933-223b-4da0-876d-6655146c0733/ssis-package-working-in-bids-but-not-with-dtexec-error-dtseadodestfailedtoacquireconnection

http://social.technet.microsoft.com/Forums/sqlserver/en-US/26dd27cf-a270-484a-9dd1-19dd777bbdd1/able-to-run-the-ssis-packages-from-bids-2005-but-not-from-sql-server-2005-agent-job?forum=sqlintegrationservices

HTH

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

Sushant Yadav (3/20/2014)
Hi All,
I am Getting following error when I execute SSIS package through SQL agent job.
Executing the query "DECLARE @errorcode INT DECLARE @ErrorDesc Varchar(..." failed with the following error: "Must declare the scalar variable "@file_name".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error Error: 2014-03-20 05:55:51.74 Code: 0xC002F210 Source: XXXXXXXXX Execute SQL Task Description: Executing the query "exec proc_PullActiveClients_exception_commodity" failed with the following error: "Column name or number of supplied values does not match table definition.". 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). Started: 5:30:09 AM Finished: 5:55:51 AM Elapsed: 1542.05 seconds. The package execution failed.
But when i execute the same from BIDS it executes fine.

Are you sure the same procedure on the same server is called when executing in Agent job?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sushant Yadav SSCrazy Points: 2780 More actions · Answer 3

Yes I have configured the same package to run from sql agent.

It was executing successfully before last two days.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

Sushant Yadav (3/21/2014)
Yes I have configured the same package to run from sql agent.
It was executing successfully before last two days.

Does the user executing the job have the required permissions?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sushant Yadav SSCrazy Points: 2780 More actions · Answer 5

Yes, tried to execute the same using Admin account.