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

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

The account who sql server agent runs under it, has privilege on the provided network path?http://www.rad.pasfu.com

"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).

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. 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

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.

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

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/

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

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

32-bit version ? i'm lost, would that mean i would have two instances of SSIS on the server. How & where do i install?

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

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

Uri - I tried without the ".exe" but no luck RamJaddu - DTExec.exe does not exists in folder "90" nor "80" Jeff - DTExec Exists ? :(

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

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?

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

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?

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

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

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

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?

also for permission issue please visit here http://support.microsoft.com/kb/918760 http://uk.linkedin.com/in/ramjaddu

where can i go to see if the Inetrops has installed & registered? do i need to insatll using BIDS Tool ( .NET Programmability Support ) ?

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 ?

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 ?

Is the Proxy user Domain Account based with all the necessary rights? Besides, what is in that ActiveX script?Arthur My Blog

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 ??

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

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

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!

No did not work, installed driver & was already installed on server & still not winning :(

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

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

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

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

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

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

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/

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