none
No logging when running package from job!!

    Question

  • We are facing an issue when deploying some packages to the server. Our packages are deployed to File System in SSIS package store.

    We have defined a logging connection to MSDB in all packages and use that to log to msdb..sysssislog. Also, a config file(for each package) is used to store the connections and other variables

    Problem is, when we schedule the package through a job(using SSIS Package step type) , giving the config file...the package executes but it doesnt log anything to sysssislog.

    However, if I right click on the package in the Filesystem store and run it, providing the same config file. It runs and also logs..

    We are trying to figure out why it wont log from the job

    Monday, August 23, 2010 4:37 PM

All replies

  • Does the sql agent service account or the "runas" account have permission to write to the log table in msdb?  When you run the package in BIDS you are running as yourself and I assume you are sysadmin in the server.  But the service account shouldn't be.


    Russel Loski, MCT
    Monday, August 23, 2010 5:04 PM
  • In your Configuration, are you setting a Username and Password for your connection to the MSDB database, or changing it in any way? Is it possible that you EXPECT the connection to point to a PRODUCTION copy of MSDB, when in fact it is still pointing to TEST or DEV? Just a thought. Are you getting any errors in the job?
    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Monday, August 23, 2010 5:46 PM
  • Thanks for your reply Russel.

    Could you please guide me how I can check this? The owner of the job is an sa account (having sysadmin priviliges) and the package step is run as "SQL Server Agent Service Account"

    Also, since we are providing the connection credentials for the msdb log connection (through sql authentication) in the config file, wouldnt that account be used to write to the log table?

     

     

    Tuesday, August 24, 2010 7:52 AM
  • Thanks Todd.

    We are providing the production credentials to msdb connection in config file. Also, I double checked that it isnt getting logged to DEV/UAT while the package is executing.

    Also, the job isnt giving any errors. I see the message "The package execution returned DTSER_SUCCESS" in job history. no errors

     

    Tuesday, August 24, 2010 8:01 AM
  • If the connection string is in the config file and it has both the user name and the password, that should be the connection used.

    Just to check.  Do you have evidence that SSIS is reading the config file?  I wonder if perhaps it isn't finding the config file.

     


    Russel Loski, MCT
    Tuesday, August 24, 2010 11:27 AM
  • If the connection string is in the config file and it has both the user name and the password, that should be the connection used.

    Just to check.  Do you have evidence that SSIS is reading the config file?  I wonder if perhaps it isn't finding the config file.

     


    Russel Loski, MCT
    Yes it is reading the config file since there are other file connections and db connections that are getting picked up right, its just the logging that aint working
    Tuesday, August 24, 2010 1:09 PM
  • Hi,

    I suspect the issue is in accounts. Specifically I suspect the accounts have different privileges, when you run the job you have rights to write to the MSDE, but when the job runs from within the SQL Server Agent - the agent has no rights to write to the MSDE. Please confirm or deny.


    Arthur
    Tuesday, August 24, 2010 7:07 PM
    Moderator
  • Hi,

    I suspect the issue is in accounts. Specifically I suspect the accounts have different privileges, when you run the job you have rights to write to the MSDE, but when the job runs from within the SQL Server Agent - the agent has no rights to write to the MSDE. Please confirm or deny.


    Arthur
    But we have created a seperate connection to MSDB and are using that connection to perform the logging. So it shouldn't matter if the package itself is runnig from my id or the service account. should it?
    Wednesday, August 25, 2010 1:56 PM
  • But we have created a seperate connection to MSDB and are using that connection to perform the logging. So it shouldn't matter if the package itself is runnig from my id or the service account. should it?
    It might matter indeed, for example the database security is set so it disallows inserts/updates.
    Arthur My Blog
    Monday, August 30, 2010 8:29 PM
    Moderator
  • I logged in as the service account into management studio(nt auth) and tried to insert a test row into msdb..syssislog on the  production server. And it worked!

    Meaning that  the service account does have the rights to write to this table.

    Any other ideas anyone?

    Friday, September 03, 2010 11:50 AM
  • Run a SQL Profiler Trace against the target log server and see what connectionsd and commands come in when you start the package. Try not to filter it too much in case you miss something.

    Have you got job step logging on? See the Advanced tab of the step. Turn that on and review the output, and perhaps post it here.

    You may also wish to turn on all the logging options in your package, as maybe some of the information evenst could provide a clue to what is happening that you may not have realised.

     


    http://www.sqlis.com | http://www.konesans.com
    Monday, September 06, 2010 7:57 AM
    Moderator
  • Hi all,

    This issue is now resolved. Strange , but turns out it was due to SSIS components not being installed on our SQL SERVER(the one where the job resides). Our packages are deployed to another server.

    In development, the sql server had ssis installed so the issue didnt occur.

    However, we do have another issue where not all tasks are getting logged in case the package succeeds. I think i'll probably raise it in onther thread.

    Thanks for all your help everyone

     

    Tuesday, September 07, 2010 2:34 PM