Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2198
  • Last Modified:

Having problems with scheduling DTS jobs in SQL Server 2005

I have problem in scheduling the dts jobs in SQL Server 2005.

I have migrated from SQL Server 2000 to SQL Server 2005 along with 10 dts jobs. These dts jobs work fine when I execute in SQL Server Management Studio. When I schedule using SQL Server Agent, it fails. I tried this using windows authentication and also SQL Server user. When I look into the history, I am not able see any meaning ful message. I says "The package execution failed. The step failed". These dts pacakges has dos commands like pkunzip, xcopy, have VB code and transformations.

I created a package using SQL Server Business Intelligence Development Environment. and tried using dtexec and dtsrun. But it not working.

I have migrated these applications using windows authentication. When I migrate using user accounts, I get error code 0x80040E14 (Access to Integration Services Package is denied).

Can someone give me the right solution? This is very urgent.


1 Solution
I do not see anyone helping... so let me offer some suggestions.

I believe that ssis runs the dts programs now. In my install that is assigned a "network user" userid. make sure that userid has access to the objects in your dts package. You may have to simply crank up the security for that login. Alternative - run the ssis under a more powerful user....

I am not expert on this yet.. just offering some ideas...

try executing this command and check the log returned
select * from msdb..sysjobstepslogs
See if this helps.

Create a SSIS package and save "Encrypt sensitive with password".  
Import into SSIS using default settings "Encrypt sensitive with user key".  
These settings do not appear on the dialog, but are visible when you click the '.' button.  SQL Agent Job execution fails.

Import job using "Encrypt sensitive with password" then adding /DECrypt to the Command Line allows the package to be executed.
However, now the job decryption password is now stored in the MSDB.sysjobsteps table.  
To both 1) not require User Key, and 2) not require password to Decrypt, store package with ServerStorage security.
Hi sriniram,

Try tow things.

1. Run Sql Server 2005 Agent services with domain admin account.
2. Change the owner of your job to be this domain admin.
3. In the Run As option of your Job run it as Domain Admin.
sriniramAuthor Commented:
I have resolved this problem using dtsrun. The dts package is modified in the SQL Server Management Studio and saved in the .dts file. Using dtsrun utility, I execute the job using the .dts file and it works fine.

I have used windows scheduler to schedule the jobs.

Thanks for giving me some options.

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now