Issues while executing the xp_cmdshell through SSIS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Issues while executing the xp_cmdshell...

Issues while executing the xp_cmdshell through SSIS

Rate Topic

Display Mode

Topic Options

Author

Message

skpanuganti

Posted Monday, March 12, 2012 7:34 PM

SSC Journeyman

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:04 AM
Points: 83, Visits: 50

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

Koen Verbeeck

Posted Tuesday, March 13, 2012 12:26 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:52 AM
Points: 9,372, Visits: 6,469

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

herladygeekedness

Posted Tuesday, March 13, 2012 11:39 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 8:46 AM
Points: 240, Visits: 653

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

Phil Parkin

Posted Tuesday, March 13, 2012 11:43 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 4:33 AM
Points: 4,239, Visits: 9,483

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:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1266172

michael.catalano

Posted Friday, January 11, 2013 10:45 AM

Forum 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

Tim Mitchell

Posted Monday, January 14, 2013 10:33 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 4:11 PM
Points: 1,033, Visits: 2,593

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
www.TimMitchell.net
twitter.com/Tim_Mitchell

Post #1406840

« Prev Topic | Next Topic »

Permissions