March 12, 2012 at 7:34 pm
Hi All -
First let me tell you what my SSIS package does,
SSIS Package: -
As per the business requirement, i have to run the Excel & Access Macro's thru SSIS package. So, what i did is, i have created the .vbs scripts and trying to executing them through SSIS package but unable to succeed becasue of the following error
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.
NULL
C:\LCSP Applications\Alcspscript.vbs(6, 1) Microsoft Office Excel: Microsoft Office Excel cannot access the file 'C:\LCSP Applications\partner_email.xls'. There are several possible reasons:
NULL
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.
NULL
NULL
But the interesting thing is, if i run those .vbs scripts just by double clicking them its working fine.
Note: - I have enabled the "xp_cmdshell" command also.
Any ideas will be greatly appreciated.
Many Thanks in Advance.
March 13, 2012 at 12:26 am
Why are you using SSIS for this? This is the wrong tool for the job, SSIS is used to transfer and manipulate data, not fiddle around with macro's.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 13, 2012 at 11:39 am
Why is the business requirement to run macros thru SSIS?? That's odd, to say the least. However, I have had stranger things dictated to me ...
If you really need this vba code, use a script component in a data flow. That uses VSA, and VBA is a very close match to it. not perfect, you'll have editing to do.
Otherwise, unless there are significant reasons not to, I would recreate the macro actions is SSIS if data tranforms and loading are part of the project. If you aren't moving data, I'd question why you "have to" use SSIS if you already have the macros.
More info, please.
March 13, 2012 at 11:43 am
Notwithstanding the fact that SSIS is the wrong tool - ditto comments already made ...
Have you investigated the 'Execute Process' SSIS task - this might run the .VBS scripts (never tried, but worth a shot).
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 11, 2013 at 10:45 am
Did you get this resolved? I need to do something similar but am getting the same error. I had the code working on an xp machine running ss 2005 but now that I upgraded to Server 2012 and ss 2012, I get this error. I can also run the vbs file from windows directly and it runs fine, just fails when I run it in SQL Server.
Thanks, Mike
EXEC XP_CMDSHELL 'CScript C:\folder\VBScript\DXPETE7.vbs'
Results below:
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.
C:\folder\VBScript\DXPETE7.vbs(4, 1) Microsoft Excel: Microsoft Excel cannot access the file '\\servernm\c$\folder\archive\DXPETE7.xls'. 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.
January 14, 2013 at 10:33 am
For both of the posters asking about this, I'm going to second Phil's earlier comment. If you're using SSIS for ETL, don't use xp_cmdshell to fire executables against the operating system. The Execute Process Task is a much better tool for this.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
November 28, 2013 at 4:02 am
I am having the opposite issue. I am trying to execute SSIS package using xp_cmdshell.
Can anyone please help me?
I have created SSIS package which exports data into excel file. After export excel file, I have written some code in script task to modify those file to add new line on the top and write module name on the first row.
This package runs fine when executed from integration services and from BIDS.
Now my client want to execute this package through stored procedure only without creating SQL agent job. I know its odd requirement but I have to do it. I am not able to execute it package through xp_cmdshell. it throws below error.
EXEC master..xp_cmdshell '@"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /SQL "SSISPackage1" /SERVER "Server1"
Package throws error while executing script task.
Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'E:\SSIS\EXPORT_DATA\CSV_VENDOR.xls'.
There are several possible reasons:
NULL
? 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.
Please help me I am struggling to fix this issue.
November 28, 2013 at 4:16 am
hardikr (11/28/2013)
I am having the opposite issue. I am trying to execute SSIS package using xp_cmdshell.Can anyone please help me?
I have created SSIS package which exports data into excel file. After export excel file, I have written some code in script task to modify those file to add new line on the top and write module name on the first row.
This package runs fine when executed from integration services and from BIDS.
Now my client want to execute this package through stored procedure only without creating SQL agent job. I know its odd requirement but I have to do it. I am not able to execute it package through xp_cmdshell. it throws below error.
EXEC master..xp_cmdshell '@"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /SQL "SSISPackage1" /SERVER "Server1"
Package throws error while executing script task.
Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file 'E:\SSIS\EXPORT_DATA\CSV_VENDOR.xls'.
There are several possible reasons:
NULL
? 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.
Please help me I am struggling to fix this issue.
Does that path (E:\SSIS\EXPORT_DATA) exist on the server?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 28, 2013 at 4:23 am
Thanks for reply.
yes, Path and file both are exists when package runs.
OS installed on server is Windows Server 2008 R2.
My package runs under user SQLServices and I have given full rights to folder for this user.
November 28, 2013 at 4:57 am
hardikr (11/28/2013)
Thanks for reply.yes, Path and file both are exists when package runs.
OS installed on server is Windows Server 2008 R2.
My package runs under user SQLServices and I have given full rights to folder for this user.
And is E: a local or network drive?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 28, 2013 at 5:16 am
E: is local drive
November 28, 2013 at 10:39 am
And does the agent account (or the service account if you haven't set up an agent account fr xp_cmdshell) have write permissions for that file and path?
Tom
November 28, 2013 at 9:12 pm
Thanks for reply.
I don't know how but creating following directory fixed my issue..
C:\Windows\SysWOW64\config\systemprofile\Desktop
November 30, 2019 at 8:53 pm
Old post but thanks, this was the issue for me. How did you know it was a missing directory?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply