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.
