none
32 Bit Executing Operating System(CmdExec) on the server

    Question

  • Hi, i need help with below command - on previous sql2005 server the command worked & now struggling on SQL2008 Server

    32 Bit Executing Operating System(CmdExec) on the server

    Type: Operating System (CmdExec), Run as: SQL Server Agen Service Account

    All i now changed was the location of the DTSExec being in folder "100" & Server Name

    "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /SQL "\Daily\Test Excel" /SERVER "XXX-ABC" /CHECKPOINTING OFF  /REPORTING E

    Gives me errors with possible reasons: file name or path does not exist,cannot access file, file being used by another program, workbook same name as currently open workbook

    It cant be as the packange runs fine on the server when imported in SSIS or BIDS

    Please Assist on what i'm doing wrong

    Also had the Job run in 32 bt - Integration Service Pkg - Execution Options - Use 32-Bit Runtime




    • Edited by Ismailc Friday, May 20, 2011 10:00 AM
    Monday, May 16, 2011 2:06 PM

Answers

All replies

  • Did you use a domain based proxy in the SQL Agent job that has access to this directory?

    Also, if you run this package off the file system using a batch file, will it execute?


    Arthur My Blog
    By: TwitterButtons.com
    Monday, May 16, 2011 3:08 PM
  • The account who sql server agent runs under it, has privilege on the provided network path?
    http://www.rad.pasfu.com
    Monday, May 16, 2011 4:42 PM
  • "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /SQL "\Daily\Test Excel" /SERVER "XXX-ABC" /CHECKPOINTING OFF  /REPORTING E


    I just want to know that the path of the package is in within SQL SSIS (Integration services)? and the package name and path is correct which is "\Daily\Test Excel"? and its not in MSDB path?

    am i right ?can you please check? thanks


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    Monday, May 16, 2011 5:01 PM
  • 1. Plz advice how i would find the account that Agent runs under & give rights to that network path?

    2. How would i check if the domain based proxy?

    3. How do i run using batch file, (do you mean the command line when executing in Integration services)?

    Nik - Error, Cannot Find Folder : \MSDB\Daily\Test Excel

    Thank You All, patiently awaiting your response.

    Tuesday, May 17, 2011 6:43 AM
  • 1. Plz advice how i would find the account that Agent runs under & give rights to that network path?


    in the Run, type Services.msc , in the services window, find the sql server agent service. double click on it, in the logon tab you will find the account who runs this service.

    then you should go to the network path with admin right and set permission to read/ or write to the sql server agent account.

     

     


    http://www.rad.pasfu.com
    Tuesday, May 17, 2011 6:48 AM
  • 1. Plz advice how i would find the account that Agent runs under & give rights to that network path?

    2. How would i check if the domain based proxy?

    3. How do i run using batch file, (do you mean the command line when executing in Integration services)?

    Nik - Error, Cannot Find Folder : \MSDB\Daily\Test Excel

    Thank You All, patiently awaiting your response.


    1. See SQL Server configuration Manager in the start menu (under SQL Server --> Configuration Tools). Check the SQL Server Agent service in the Services tab.

    2. In he SQL Agent job, did you select an acount under which the job should run?

    3. Batch file --> directly execute the package using the dtexec command line utility


    MCTS, MCITP - Please mark posts as answered where appropriate.
    Tuesday, May 17, 2011 6:51 AM
  • Thank You but no luck & running out of time.

    The SQL Agent Account uses the Server Admin Account, i logon using this account & execute the package in SSIS & BIDS Successfully.

    I changed the Job account to myself (knowing i have rights to the path) & i get the same error.

    Trying the Batch file now - have no clue but going to see

    Please help, i'm not winning & running out of time

    Tuesday, May 17, 2011 7:16 AM
  • Should it be 

    "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec" --without .EXE?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, May 17, 2011 7:25 AM
  • Should it be 

    "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec" --without .EXE?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, May 17, 2011 7:25 AM
  • Should it be 

    "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec" --without .EXE?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, May 17, 2011 7:25 AM
  • temporarly off the server (one admin account) - will try now in few minutes.

    i tried executing the pkg from network path using a bacth, but dont work :

    DTExec /FILE "\\...\Sales.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

    Tuesday, May 17, 2011 7:49 AM
  • Have you actually got the 32-bit version of SSIS installed on your server?
    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    Tuesday, May 17, 2011 8:23 AM
  • 32-bit version ? i'm lost, would that mean i would have two instances of SSIS on the server.

    How & where do i install?

    Tuesday, May 17, 2011 8:36 AM
  • To select the 32-bit version of the DTExec.exe utility to run the package, you do not have to type any command-line switches. Instead, you must only provide the path of the 32-bit version of the DTExec.exe utility. To do this, follow these steps:

    Try this -

    "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /SQL "\Daily\Test Excel" /SERVER "XXX-ABC" /CHECKPOINTING OFF  /REPORTING E

     

     

    More from here how to execute 32bit SSIS http://support.microsoft.com/kb/934653



    http://uk.linkedin.com/in/ramjaddu
    Tuesday, May 17, 2011 8:48 AM
  • Usually servers run under 64-bit which means that "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec" may not exist as (x86) relates to 32-bit OS.  Can you confirm that DTExec exists in the direcotry "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\"
    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    Tuesday, May 17, 2011 8:49 AM
  • Uri - I tried without the ".exe" but no luck

    RamJaddu - DTExec.exe does not exists in folder "90" nor "80"

    Jeff - DTExec Exists

    :(

     

    Tuesday, May 17, 2011 9:24 AM
  • Then please see this http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx

    http://uk.linkedin.com/in/ramjaddu
    Tuesday, May 17, 2011 9:30 AM
  • RamJaddu - i get the same error - " file name or path does not exist,cannot access file, file being used by another program, workbook same name as currently open workbook"

    How can i make sure that the Agent Account has access to the path?

    Tuesday, May 17, 2011 9:58 AM
  • I have tried everything, the DTSExec.exe exists & command line is correct as i used the command line genrated when adding to agent with an Integration pkg, also tried different user.

    But no luck - this pkg works when executing in BIDS & SSIS on the server

    The pkg executes an excel 2007 macro xlsm

    ERROR: Microsoft Office Excel cannot access the file '\\sbh-fs02\projects\it\Systems\BI\AutomationFiles\Excel\Daily Sales\MacroDS.xlsm'. There are several possible reasons:    ? The file name or path does not exist.  ? The file is being used by another program.  ? The workbook you are trying to save has the same name as a currently open workbook.     at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename<c/>

     

    Please Help

    Tuesday, May 17, 2011 10:25 AM
  • How do I install the 32Bit version as it's not found in the "C:\Program Files (x86)\Microsoft SQL Server\90\"

    How & where do i find the files?

    Tuesday, May 17, 2011 10:35 AM
  • It now appears that the SQL Server Agent Account does not have permissions to access \\sbh-fs02\projects\it\Systems\BI\AutomationFiles\Excel\Daily Sales\MacroDS.xlsm.  You will need to use a Proxy Account to run the package under.

    1. Under the Security folder in the Database Server in SQL Server Management Studio, find the Credentials folder.
    2. Right click Credentials and hit New Credential.
    3. Set up the Credential as you.
    4. Expand SQL Server Agent, and right click Proxies, and hit New Proxy.
    5. Set up the Proxy as you, using the Credential you just created.
    6. Enable the Proxy to have the SSIS subsystem (if it's admin, check all of them).
    7. Go edit the step on your job that calls the SSIS package.
    8. Change the Run As field from SQL Server Agent to the Proxy that you just set up (if you did it right, it will be the only other choice in the dropdown).
    9. You should be good to go!


    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD

    Tuesday, May 17, 2011 10:49 AM
  • Wow - Thank You for teaching me things here.

    Unfortunatly not winning, get Error: CANNOT CREATE ACTIVEX COMPONENT


    If i setup proxy account with my UserID & with all subsystem checked, i get error: Cannot Create ActiveX Component

    The same but with Admin Account, i get the error: cannot find the file

    Tuesday, May 17, 2011 11:15 AM
  • This means that the required 2007 Microsoft Office system Primary Interop Assemblies (PIA) are not installed on your SQL Server.

    Download from http://www.microsoft.com/downloads/en/details.aspx?familyid=59daebaa-bed4-4282-a28c-b864d8bfa513&displaylang=en


    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    Tuesday, May 17, 2011 11:27 AM
  • But it runs via BIDS & SSIS on the server ?

    I download & clicked on the file MSI - where do i check to see if it's installed?

    Tuesday, May 17, 2011 11:29 AM
  • also for permission issue please visit here

    http://support.microsoft.com/kb/918760

     

     

     


    http://uk.linkedin.com/in/ramjaddu
    Tuesday, May 17, 2011 11:29 AM
  • where can i go to see if the Inetrops has installed & registered?

    do i need to insatll using BIDS Tool ( .NET Programmability Support ) ?

    Tuesday, May 17, 2011 11:32 AM
  • I see the Microsoft.Office.Interop.Excel in "c:\windows\assembly" but nothing in the "C:\Windows\Microsoft.NET\Framework64\v3.5"

    Also not to sure if the interop installation .msi installed it in the assmebly folder - more the Excel installation that put it there.

    Please help ?

    Tuesday, May 17, 2011 1:31 PM
  • Hi, I'm not to sure if i'm making progress.

    The pkg runs via Integration Services & BIDS on the server but not Agent.

    When i create the job, using SQL Server Agent User i get error: cannot find path

    but same job with Proxy User i get error: Cannot Create ActiveX Component

    ?

    Tuesday, May 17, 2011 2:56 PM
  • Is the Proxy user Domain Account based with all the necessary rights?

    Besides, what is in that ActiveX script?


    Arthur My Blog
    By: TwitterButtons.com
    Tuesday, May 17, 2011 3:25 PM
  • Yes, the Proxy user is a domain account with all rights. The script open excel and executes a macro that works when i execute the macro in Excel on the new server. The pkg works on the server in BIDS & Integration Services on the server hich i think is 64bit - but when i run the pkg as 64bit on the sql agent then it complain about the bit mode

     

    ??

    Wednesday, May 18, 2011 1:04 PM
  • What does the macro do?  Are you able to post the macro code as there may be some code inside the macro that is trying to execute or use an object that has not been installed on the server.
    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    Wednesday, May 18, 2011 1:10 PM
  • Sub DS()
        Range("A1").Select
        ActiveSheet.PivotTables("PivotTable1").PivotCache.RefreshOnFileOpen = True

        Sheets("Sheet1").Select
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        Cells.Select
        Selection.Delete Shift:=xlUp
        Range("A1").Select
        Sheets("Daily Sales").Select
       
        Range("A1").Select
        ActiveSheet.PivotTables("PivotTable1").PivotCache.RefreshOnFileOpen = False
       
       
        ActiveWindow.LargeScroll Down:=1
        Range("BU2708").Select
        ActiveWindow.ScrollRow = 17
        ActiveWindow.ScrollRow = 16
       
        ActiveWorkbook.SaveAs Filename:="\\abc\Sales.xlsx", FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
    End Sub

     

    Wednesday, May 18, 2011 1:28 PM
  • I found this post: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/ca20174a-697b-44f3-a9d5-e7e992017071

    Needing to Install Excel 2007 Driver, but Excel is on the server!

    Wednesday, May 18, 2011 2:13 PM
  • No did not work, installed driver & was already installed on server & still not winning :(
    Wednesday, May 18, 2011 2:23 PM
  • same problem here (http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/7fcbfc4b-2211-47b0-9e17-60266d5923b4)

    But Excel is installed on the server & still no solution

    Wednesday, May 18, 2011 2:40 PM
  • Excel has has no 64 bit driver, hence you need to run your package in 32 Bit mode. This is the fix.

    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, May 18, 2011 2:56 PM
  • I have the job setup with Proxy user as Integration Service Package & have the 32-Bit checkbox checked and i get the error with the macro script: Cannot Create ActiveX Component
    Wednesday, May 18, 2011 3:11 PM
  • Check if this account has access to writing into the TEMP directory on the server, albeit I now think it could also be security related.

    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, May 18, 2011 3:13 PM
  • Is there anyway you could provide your package and MacroDS.xlsm file?
    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    Wednesday, May 18, 2011 11:35 PM
  • Have a look at this blog post as someone else was experiencing similar problems as you and this fixed it for them:

    http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/01/22/the-ssis-and-excel-story-continues.aspx


    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    • Marked as answer by Ismailc Thursday, May 19, 2011 6:55 AM
    Thursday, May 19, 2011 2:08 AM
  • Woot, woot, woot.  Now I can rest in peace :-)

    Glad to see we figured it out.  Thanks for marking your question answered.


    Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
    Thursday, May 19, 2011 7:41 AM