Post reply

SSIS Jobs running under SQL Server Agent

  • December 17, 2007 at 6:34 am

    #71203

    I would be grateful for some help.

    I have built several jobs under SSIS. These jobs are mainly jobs getting data from another Server. It would be ideal to have these run under SQL Server Agent overnight. When I try however they fail.

    Has anyone got any suggestions?

    David Halliday

  • December 17, 2007 at 9:38 am

    #760740

    Do you get an error when the job fails? If so, please post the error message here.

    Thanks

    Greg

    Greg

  • December 18, 2007 at 7:02 am

    #760991

    Gregg

    Thank you for your interest. I have a SSIS Job with six steps. The first five steps run stored procedures to truncate tables 1 to 5. Step 6 goes to another Server with a SSIS dtsx task and reloads table 1

    The error message I get is the job failed at step 6. I am sure that there is a log file somewhere with more information but I can't find it. I am very new to SSIS

    Any help would be appreciated.

    Regards

    David

  • December 18, 2007 at 7:11 am

    #761000

    I have narrowed the problem down to a login problem.

    Say I login with username 'smith' on the home and remote machine.

    They have different passwords. This is accomplished through different connections on SSIS. When the job is run under SSIS it runs perfectly.

    When the job is run under SQL Server Agent it fails with a login error.

    Any further help would be appreciated.

    Regards

    David

  • December 18, 2007 at 3:23 pm

    #761248

    I suspect this is the classic "SQL Server Agent security" problem. I've found this KnowledgeBase article helpful: http://support.microsoft.com/?kbid=918760

    Greg

    Greg

  • December 19, 2007 at 3:12 am

    #761325

    Gregg

    Thanks I will try it

    David

  • January 10, 2008 at 12:20 pm

    #766804

    If using 2 different accounts on 2 different servers, use Windows authentication in your SSIS Task connection manager for both connections.

    Regards

    Kevin

  • January 11, 2008 at 1:56 am

    #766991

    Kevin

    I have approached the Senior DBA to try to get permission to do this, but this company is funny.

    Thanks for your input, I will try again

    David

  • January 12, 2008 at 10:42 am

    #767424

    Hi David

    I had a similar issue. One connection was pointing to local server (using local server's sa logon account), the other connection was pointing to another server (using that server's sa logon account). When running the SSIS task via SQL Server Business Intelligence Dev Studio (Solution explorer, SSIS Packages, right-click Package.dtsx, Execute Package) the local server sa logon account failed authentication. I changed the SSIS Task connection (connection manager) for local server to use Windows authentication, since the SSIS Task is run from local server, using either sa or Windows authentication makes no difference as far as security is concerned.

    Regards

    Kevin

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply