Issues while executing the xp_cmdshell through SSIS

  • skpanuganti

    SSC-Addicted

    Points: 456

    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.

  • Koen Verbeeck

    SSC Guru

    Points: 258955

    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

  • herladygeekedness

    SSCertifiable

    Points: 5798

    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.

  • Phil Parkin

    SSC Guru

    Points: 243862

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

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • michael.catalano

    SSC Enthusiast

    Points: 130

    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.

  • Tim Mitchell

    SSCoach

    Points: 15653

    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

  • hardikr

    SSC Veteran

    Points: 281

    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.

  • Phil Parkin

    SSC Guru

    Points: 243862

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • hardikr

    SSC Veteran

    Points: 281

    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.

  • Phil Parkin

    SSC Guru

    Points: 243862

    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?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • hardikr

    SSC Veteran

    Points: 281

    E: is local drive

  • TomThomson

    SSC Guru

    Points: 104772

    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

  • hardikr

    SSC Veteran

    Points: 281

    Thanks for reply.

    I don't know how but creating following directory fixed my issue..

    C:\Windows\SysWOW64\config\systemprofile\Desktop

  • Jake.ONeill

    Newbie

    Points: 7

    Old post but thanks, this was the issue for me. How did you know it was a missing directory?

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply