32 Bit Executing Operating System(CmdExec) on the server
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
May 16th, 2011 10:11am
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
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2011 11:12am
The account who sql server agent runs under it, has privilege on the provided network path?http://www.rad.pasfu.com
May 16th, 2011 12:44pm
"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? thanksSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
Free Windows Admin Tool Kit Click here and download it now
May 16th, 2011 1:03pm
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.
May 17th, 2011 2:44am
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
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 2:49am
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 utilityMCTS, MCITP - Please mark posts as answered where appropriate.
May 17th, 2011 2:52am
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
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 3:18am
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/
May 17th, 2011 3:27am
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
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 3:50am
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
May 17th, 2011 4:25am
32-bit version ? i'm lost, would that mean i would have two instances of SSIS on the server.
How & where do i install?
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 4:38am
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
May 17th, 2011 4:49am
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
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 4:50am
Uri - I tried without the ".exe" but no luck
RamJaddu - DTExec.exe does not exists in folder "90" nor "80"
Jeff - DTExec Exists
?
:(
May 17th, 2011 5:26am
Then please see this http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspxhttp://uk.linkedin.com/in/ramjaddu
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 5:31am
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?
May 17th, 2011 5:59am
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
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 6:26am
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?
May 17th, 2011 6:36am
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.
Under the Security folder in the Database Server in SQL Server Management Studio, find the Credentials folder.
Right click Credentials and hit New Credential. Set up the Credential as you. Expand SQL Server Agent, and right click Proxies, and hit New Proxy. Set up the Proxy as you, using the Credential you just created. Enable the Proxy to have the SSIS subsystem (if it's admin, check all of them).
Go edit the step on your job that calls the SSIS package. 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).
You should be good to go!
Jeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 6:51am
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
May 17th, 2011 7:16am
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=enJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 7:29am
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?
May 17th, 2011 7:30am
also for permission issue please visit here
http://support.microsoft.com/kb/918760
http://uk.linkedin.com/in/ramjaddu
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 7:31am
where can i go to see if the Inetrops has installed & registered?
do i need to insatll using BIDS Tool ( .NET Programmability Support ) ?
May 17th, 2011 7:34am
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 ?
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 9:32am
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
?
May 17th, 2011 10:58am
Is the Proxy user Domain Account based with all the necessary rights?
Besides, what is in that ActiveX script?Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
May 17th, 2011 11:28am
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
??
May 18th, 2011 9:15am
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
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2011 9:23am
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
May 18th, 2011 9:41am
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!
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2011 10:26am
No did not work, installed driver & was already installed on server & still not winning :(
May 18th, 2011 10:36am
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
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2011 10:42am
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
May 18th, 2011 10:58am
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
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2011 11:14am
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
May 18th, 2011 11:16am
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
Free Windows Admin Tool Kit Click here and download it now
May 18th, 2011 7:37pm
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.aspxJeff Wharton MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt) MCT, MCPD, MCITP, MCDBA, MCSD, MCAD
May 18th, 2011 10:09pm
THANK YOU ALL
Special Thanks to Jeff Wharton - my PAIN is over :)
This:
http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/01/22/the-ssis-and-excel-story-continues.aspx
together with this (DCOMCNFG 32 bit):
http://social.technet.microsoft.com/Forums/en-US/w7itproappcompat/thread/dde69147-a01a-4eb1-8ea9-31adbf874bed/
Free Windows Admin Tool Kit Click here and download it now
May 19th, 2011 2:55am
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
May 19th, 2011 3:42am