Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Issues while executing the xp_cmdshell through SSIS Expand / Collapse
Author
Message
Posted Monday, March 12, 2012 7:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 18, 2014 10:06 AM
Points: 88, Visits: 62
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.
Post #1265661
Posted Tuesday, March 13, 2012 12:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:23 AM
Points: 12,954, Visits: 10,724
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.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1265713
Posted Tuesday, March 13, 2012 11:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:05 PM
Points: 287, Visits: 810
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.

Post #1266169
Posted Tuesday, March 13, 2012 11:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 5,078, Visits: 11,860
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).



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1266172
Posted Friday, January 11, 2013 10:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 28, 2013 11:03 AM
Points: 2, Visits: 12
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.
Post #1406183
Posted Monday, January 14, 2013 10:33 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:46 PM
Points: 1,045, Visits: 2,725
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, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #1406840
Posted Thursday, November 28, 2013 4:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:49 AM
Points: 7, Visits: 66
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.

Post #1518313
Posted Thursday, November 28, 2013 4:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 5,078, Visits: 11,860
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1518317
Posted Thursday, November 28, 2013 4:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:49 AM
Points: 7, Visits: 66
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.
Post #1518319
Posted Thursday, November 28, 2013 4:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:46 AM
Points: 5,078, Visits: 11,860
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1518323
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse