Post reply

Unable to run SSIS package as a scheduled job or with dtexec.exe

  • August 23, 2006 at 3:01 am

    #656299

    Hi! I've done all the instructions that was written here but still I'm getting the same error. Can somebody help me?

    Thanks in advance.

    Kindest Regards,

    Florence

  • August 23, 2006 at 3:52 am

    #656303

    Hi Florence,

    What are the specific problems you are having? What is your environment setup?

    I'll see if I can help, although it is a bit of the blind leading the blind...

    CiaO

  • August 23, 2006 at 5:20 am

    #656318

    To resolve this issue I had to set the package to run with Windows authentication (causing it to use the SQL Agent service account). Then I set up a local account to run SQL Agent and added it to the local SQLServer2005DTSUser$machinename group as well as the local Administrators group. I created a SQL login, mapped it to this Windows user and added it to the SysAdmin role in SQL. This allowed the package to run properly. Hopefully it was not overkill but we have things locked down pretty well so I don't expect it to open us up to any vulnerability.

  • August 23, 2006 at 5:59 am

    #656323

    Imho, it sounds like that may be a bit of an overkill. I assume that the proxy and credential script I posted didn't help?

    Florence, I would recommend (unless you've tried, and it didn't help) reviewing the infor I posted in the 1st page of this thread regarding the creation of Proxy and credentials. I was able to get SQL Server Agent to run a job using a explicit user that had no greater rights than USERS (i.e. member of the local users group).

  • August 23, 2006 at 8:38 am

    #656372

    Hi

     

    We have had some same issues and still have. We have another problem. We are running sql server on a 64bit machine but it seems that the dtexec used for x64 does not work. Thus we must use the dtexec x86.

    We still have difficulties though by running those scripts automtically thorugh jobs or asp.net application.

    We already followed the course integration services and there they also told us it's a security issue. I think it's one of the weaker modules of 2005 especially if u r running on 64 bit.

    We hope to have it working soon.

    Anyone had the issues with the 64bit ?


    JV

  • August 23, 2006 at 9:37 am

    #656401

    Sorry to say that I've had no chance to play around with 64bit, so I'm going to be 0 help to you I'm afraid.

  • August 23, 2006 at 4:31 pm

    #656459

    I have had no problem running the packages directly from the agent from the file system  by simply selecting SSIS package from the drop down and filling in the package path.  Of course the package path is relative to the server, so if you are not on the server and the path doesn't exist on your machine, you will get an error, which you can ignore. You also need to fill in the package configuration path, which, interestingly enough, does show the actual server path whether you are on the server or not.

    For this to work you must first change the package protection level, and create a package configuration file.  I have been setting my protection level to DontSaveSensitive and providing passwords in the configuration file. 

     

  • August 23, 2006 at 8:28 pm

    #656481

    I've done all the instructions that was posted on he first page. i created a proxy, credentials, i used my windows account and so on but still my job failed during execution.

    Kindest Regards,

    Florence

  • August 23, 2006 at 8:30 pm

    #656482

    my package configuration was XML.

    Kindest Regards,

    Florence

  • August 24, 2006 at 4:13 am

    #656518

    Jv,

    We recently upgraded our data warehouse server to 64 bit & ran into a few issues with the dtexec but it mostly was related to what was compatible with 64 bit.  If you are doing anything in the package related to ODBC or the Jet driver, then it is not compatible with the 64 bit OS.  So you would have to call out the 32 bit dtexec utility.

    There may be other incompatibilities with 64 bit that we have not run into yet.  These 2 are the ones we hit so far.

    We also hit the problem with the package password error.  I set the package protection level to encrypt with a password.  I set the password in the package and when I saved the package to the server.  When I call out dtexec, use the /de YourPassword option.  This took away the error for us.  Initially it was a little deceiving because we were only getting this error but the real problem was the 64 bit incompatibility issue.  It just wasn't throwing an error for this.  So I kept troubleshooting for a security issue when in reality what i was trying to do would not work on 64 bit.  In most of our cases, the package would give the password error but the package would still execute successfully.  I then changed the protection level just so the error wouldn't show up in the log & get someone confused.

    HTH,

    John

     

  • August 24, 2006 at 9:36 am

    #656628

    laker_42:  I've having the same problem as you with the 64 bit.  Where do i find the 32 bit dtexec?  Anyone???




    Curtis Smith
    SQL Server DBA
    Well in worked in Theory ...

  • August 24, 2006 at 9:53 am

    #656634

    Curtis,

    Here is how you reference it within a SQL Agent Job:

    "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /dts \MSDB\PackageName /SERVER ServerName

     

    Here is how you reference it from a SP:

    EXEC

    master.DBO.xp_cmdshell '"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe " /SQ PackageName /SER ServerName'

     

    John

  • August 24, 2006 at 10:04 am

    #656639

    thanks.  as soon as clicked send i knew where it was.  Kinda' when you lock you keys in the car.  As soon as you hear the door close ... 




    Curtis Smith
    SQL Server DBA
    Well in worked in Theory ...

  • August 24, 2006 at 10:24 am

    #656643

    btw, typically, C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn will be in you path, and thus a simple dtexec.exe (or even dtexec) will work. Certainly, this is true in my environments

  • August 24, 2006 at 10:30 am

    #656645

    Wanderer,

    Not if you are in a 64 bit environment.  DTExec will defer to the 64 bit version.  Hence why you have to call out the full path name.

    John

Viewing 15 posts - 16 through 30 (of 54 total)

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