How to Create a SQL Server Job for Running a SSIS Package

  • Hi,

    I created a SQL Server Agent job to run a SSIS package every one minute, i.e I am trying to schedule a SSIS package in SQL agent 2005.

    As far as user Rights are concerned, I login as a sysadmin.

    I can run the package manually using Business Intelligents Studio but

    when I try to start the job in SQL Server Management Studio ,Following errror occurs.

    Execution of job 'Job Name' Failed. See The History log for details

    and in History I get the following Message

    The job failed. The Job was invoked by User sa. The last step to run was step 1 (AddRecord)

  • The job failed. The Job was invoked by User sa. The last step to run was step 1 (AddRecord)

    Check, for what reason the step 1 (AddRecord) of the job is failing?

    --Ramesh


  • My first guess would be a permissions issue. Then you are running it from BIDS, you are probably logged in with a different user than it is running under from the Job Agent. By default, the Job Agent will run a job in the context of the service account. If you are using integrated security in a connection string within the package, the permissions for the Job Agent may be insufficient.

    I would suggest you turn on the logging in the SSIS package so you can see the package errors.

    In addition, the idea of having the job agent run once per minute to execute an SSIS package seems like a very antiquated design. If this package will constantly be connecting, running some queries, doing some inserting and updating, and then disconnecting, it is likely o be a constant performance hit. If you are trying to wait for something to happen and then act on it in "real time", there are better approaches. Application developers started using event driven programming decades ago.

  • This can also be affected by how you refer to objects outside of SQL (such as files) or even passwords that may be stored within your package. Make sure you tick the box to use SQL Server for encryption if deploying to SQL, and use full path names to access any files needed


    Kindest Regards,

    Catherine Eibner
    cybner.com.au

  • will it run if you use dtexec from the command line, running from the server logged in as the same user that runs Agent jobs? does your package use configuration files? Does your package access any data sources for which you need to store logon credentials?

    When you run in BIDS, SSIS will "remember" your data source logon credentials. But when you run it from an external source such as dtexec or Agent, it will not remember these credentials. This is one common reason for packages to fail when run outside of BIDS when they run fine within BIDS.

    If you are using configuration files to store passwords, you will sometimes need to manually edit the connection strings to add the passwords (depends on type of authentication). If you edit a config file to add a password, then every time you edit the contents of the config file using BIDS you will need to again manually edit the file to add the password. I try to never edit config files with BIDS after they are initially created.

  • Refer the below link, i think this may help you.

    Scheduling SSIS package

Viewing 6 posts - 1 through 5 (of 5 total)

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