Having problems with scheduling DTS jobs in SQL Server 2005

Posted on 2006-05-21
Last Modified: 2013-11-30
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.


Question by:sriniram
    LVL 10

    Expert Comment

    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...
    LVL 39

    Expert Comment


    try executing this command and check the log returned
    select * from msdb..sysjobstepslogs
    LVL 3

    Accepted Solution

    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.
    LVL 28

    Expert Comment

    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.

    Author Comment

    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

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now