Job fails but package will run from MSDB

I created a package for importing data from a flat file to an existing table in SQL Server 2005 using Management Studio and the administrator account working directly on the server through a remote login. I saved the package to MSDB.

I can see the package listed when I connect to Integration Services through Management Studio and expand the MSDB folder. In that location I have the option to run the package and it will run successfully. However, I cannot edit the package.

When I try to create a scheduled job using Sql Server Agent, where the first step is to run the package using the administrator login, the execution fails, and the job history will only tell me that the step failed. The Sql Server Agent log shows that the job started and completed. No error is shown. The Administrative Tools Event Viewer Application log shows only that the job started and failed. Security log shows no errors. System log shows no errors.

What am I doing wrong? How can I edit and schedule this package?

You cannot edit packages through SSMS, that is by design. BIDS is the design environment for SSIS, in fact for all the BI stuff.

When you schedule the package you are potentially changing the location and context compared to a right-click execute. It runs under the SQL Server agent service account, although may be using a SQL Agent Proxy, check the job. It also runs on the SQL Server machine itself.

The best thing to do its supply a step log file, or log to table. This needs to be set at a step level, and normally gives more than the job history. I would also change to CmdExec and DTEXEC. See link for an example of why-

http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

|||I don't have a proxy set up - do I need to do that? Also, what are the steps to set up the log file you mentioned?|||

If your job is owned by a sysadmin, and you don't have any special security requirements, then you do not need a proxy. It will run under the context of the SQL Server Agent service account.

To set the job step logging options, open the job, and then open the step. Select the Advanced tab, and set some options. I personally find output log file is the simplest to use and manage.

|||If part of the job is to create a destination table in the database, that part of the job will work correctly, but the data will not be imported from the source text file. I think the problem is that the SQL Server Agent doesn't have permission or security set somewhere so that the agent can read the source file directory. How can I fix this issue?|||

the issue was solved by changing encription option and changing the file reference from a drive-specific source (X:\ImportData\FileName to \\HostServerName\Directory\FileName.

Job fails but package will run from MSDB

I created a package for importing data from a flat file to an existing table in SQL Server 2005 using Management Studio and the administrator account working directly on the server through a remote login. I saved the package to MSDB.

I can see the package listed when I connect to Integration Services through Management Studio and expand the MSDB folder. In that location I have the option to run the package and it will run successfully. However, I cannot edit the package.

When I try to create a scheduled job using Sql Server Agent, where the first step is to run the package using the administrator login, the execution fails, and the job history will only tell me that the step failed. The Sql Server Agent log shows that the job started and completed. No error is shown. The Administrative Tools Event Viewer Application log shows only that the job started and failed. Security log shows no errors. System log shows no errors.

What am I doing wrong? How can I edit and schedule this package?

You cannot edit packages through SSMS, that is by design. BIDS is the design environment for SSIS, in fact for all the BI stuff.

When you schedule the package you are potentially changing the location and context compared to a right-click execute. It runs under the SQL Server agent service account, although may be using a SQL Agent Proxy, check the job. It also runs on the SQL Server machine itself.

The best thing to do its supply a step log file, or log to table. This needs to be set at a step level, and normally gives more than the job history. I would also change to CmdExec and DTEXEC. See link for an example of why-

http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

|||I don't have a proxy set up - do I need to do that? Also, what are the steps to set up the log file you mentioned?|||

If your job is owned by a sysadmin, and you don't have any special security requirements, then you do not need a proxy. It will run under the context of the SQL Server Agent service account.

To set the job step logging options, open the job, and then open the step. Select the Advanced tab, and set some options. I personally find output log file is the simplest to use and manage.

|||If part of the job is to create a destination table in the database, that part of the job will work correctly, but the data will not be imported from the source text file. I think the problem is that the SQL Server Agent doesn't have permission or security set somewhere so that the agent can read the source file directory. How can I fix this issue?|||

the issue was solved by changing encription option and changing the file reference from a drive-specific source (X:\ImportData\FileName to \\HostServerName\Directory\FileName.

Job Failes due to Error = -2147467259 (80004005)

Hello,

I am importing an excel spreadsheet into SQL Server 2000. The dts package runs perfectly when you execute it under the Local Package pane.

My problem is when i schedule a job to execute the DTS package giving me the same error. I can only successfully execute the package under local Packages. Under Jobs i receive this error:

Executed as user: H-OB\Administrator. ...rt: Copy Data from 'Xagent - Report$' to [CIMPRO1].[dbo].[Xagent_Star_Coater_Points] Step DTSRun OnError: Copy Data from 'Xagent - Report$' to [CIMPRO1].[dbo].[Xagent_Star_Coater_Points] Step, Error = -2147467259 (80004005) Error string: 'R:\shared\star coater\Monthly Points Rpt\Current_Month_Points.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft JET Database Engine Help file: Help context: 5003044 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -534774783 (E01FFC01) Error string: 'R:\shared\star coater\Monthly Points Rpt\Current_Month_Points.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft JET Database Engine ... Process Exit Code 1. The step failed.

Any help on this error would be more than appreciated.The dts package runs perfectly when you execute it under the Local Package pane.
With this I assume you mean that it works when you execute it from your workstation which is not the SQL Server Machine.

At that moment the package is execute on your own computer (not at the server), under your own credentials.

When the job executes it's running under SQL Server credentials at the server and it seems it has never heard of an R-drive at that end or has no access to the mentioned folders or files. Make sure the account under which the SQL Server service is running can access that file.|||Thanks for the advice. I looked at the accounts that is used for the mssqlserver service and that was fine.

It turns ou that for some reason the server is not pickin gup the mapped drice. I had to type the full path in so that it could recognize the file that i am retreiving form the other server.

Thanks again.