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?

Run report without Login

Is there any way to run a report via a URL without having the user login?

Could I provide special parameters on the url or define a user/password in the report itself?

We do NOT use active directory.

Thanks.

You can store the credentials needed for the data source access on the report server.

Check this BOL link: http://msdn2.microsoft.com/en-us/library/ms160330(SQL.90).aspx
Also check the links at the bottom of that page which describe how to manage data source credential settings via Report Manager and Management Studio.

-- Robert

|||

I have the data source setup ok - that's not what is generating the login request. It's report server itself and it's not obvious how I can get around that.

|||

Most likely, in IIS the virtual roots for /reportserver or /reports have a directory security configuration -> authentication methods different from "Integrated Windows Authentication" only. So it is really IIS asking to login - not Reporting Services.

-- Robert

|||That's it - thanks.