Running an SSIS package as a job that connects to a DB

I have a package that I want to run as a scheduled job in sql server 2005. The job runs fine in the studio. I am convinced that it is not a password or security issue as I set the protectionlevel to do not save sensitive data and can still run the job in the studio. The User running the job has SA privileges as does the SQL Server Agent. The job reads a flat file, runs it through a script and than inserts the rows into a table. The job runs successfully only if the flat file is empty. The job history says: The return value was unknown. The process exit code was -1073741795. The step failed.

There are no OnError lines in the logging.

Anyone have any Ideas or tricks to try.

Hi, have you seen this article?

http://support.microsoft.com/?kbid=918760

Your issue does still sound 'permission' related. Perhaps not an issue with a SQL connecton but maybe access to the file share where the files are located. One thing the article points out is how to get some logging data back to the console\file via executing the SSIS package using the command exec sub system (in agent) rather than the SSIS subsystem.

Hope that helps

|||

Yes I have seen that article.

The account sql server agent runs under has file permissions to the files and is has Sys Admin privs in SQL. I tried logging into the dev studio using that account and can run the package just fine.

|||Anything new about this? I got the same Problem and in fact have completely lost a whole day because of this completele useless and meaningless type of errorhandling. >:(|||

Is there any resolution for this?

Executed as user: xxxxxx. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 12:09:19 PM. The return value was unknown. The process exit code was -532459699. The step failed.

|||

HeatherS2006 wrote:

Yes I have seen that article.

The account sql server agent runs under has file permissions to the files and is has Sys Admin privs in SQL. I tried logging into the dev studio using that account and can run the package just fine.

But it's not the account that Agent runs under, it's the account that SQL Server runs under that you need to look at.|||

any resolution?

It's been like this since August?

|||

The account is a domain account. It is part of the Local Administrator Group.

It belongs to the BUILTIN/Administrators in SQL.

Did I overlook anything?

sql