Post reply

Job Execution failed using environment variables with SQL authentication

  • August 24, 2009 at 11:37 pm

    #209907

    Hi Friends,

    I have some problem with job execution for my SSIS package.

    i have around 30 packages in my project, for all packages the database connection strings are same.

    so i want to take these connection strings from environment variables. that's why i created 2 env variables, and taken these variables in my package configuration. i used SQL Server authentication in my package connection managers for both connection strings.

    Then i deployed my packages and trying to execute using SQL server agent job. but the job execution is getting failed. where as when i use windows authentication in my package connection managers and env variables, the job is executing successfully.

    but i am not getting why the job execution is getting failed with sql auth, and the job is executing successfully with win auth.

    can anyone help me in this regard?

    Thanks

    Sathish B

  • August 25, 2009 at 12:45 am

    #1044002

    Turn on logging and log your connections strings, then check them in the log to make sure that they are actually loading and using them and that they are using the correct ones. Among other things, it is possible that your Agent Jobs just are not finding your config files.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 25, 2009 at 12:57 am

    #1044005

    HI Young,

    thanks for your reply

    In the post, you said that, my agent job is not finding the config files right?

    but when i use windows auth for the same package the job is executing successfully, means here my job is finding config files right? so it should find the configs for sql auth also right?

    so, i think, it is not the problem of configurations.

    but the package is running successfully from the BIDS, using env variables with sql authentication.

    so what is the problem with the job?

    Thanks

    Sathish B

  • August 25, 2009 at 1:12 am

    #1044010

    I am telling you that it *might* be the problem and that you should check it to be sure. There are about 20 or 30 different things that could be wrong here, I am suggesting that you start by checking some of the more likely possibilities.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 4 posts - 1 through 3 (of 3 total)

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