SQL Server Performance

xp_cmdshell to execute SSIS Package

Discussion in 'General Developer Questions' started by adyseven, Jan 12, 2009.

  1. adyseven New Member

    Dear All,
    Do u have any recommended article or link for me to find a way to execute a SSIS package using xp_cmdshell syntax?? Please ur suggestion guys...any help i appreciated a lot...
  2. MohammedU New Member

  3. adyseven New Member

    Dear Mohammed and DBA's,
    Thx for the link, I've checked and tried it with my own example. There are some question..please help...
    Using BIDS, I have deployed and built an SSIS Package (named: cmdshell_test.dtsx). After previous step, how can we see them in SSMS (Integration Services)? I could not find it in Stored Packages -> File System or MSDB. I have tried to import package too, but it is not working.
    Do I have to save the package in some default folder or can be any folder? (I deploy it in server local drive named F:DMA)
    Then, at server, using login 'sa', I used these syntaxes for executing the packages (i copy it from the link that u gave me)
    declare @ssisstr varchar(8000), @packagename varchar(200), @servername varchar(100)
    set @packagename = 'F:dmacmdshell_test.dtsx'
    set @servername = 'a000s-xxxx'
    set @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' '
    --print @ssisstr
    DECLARE @returncode int
    EXEC @returncode = xp_cmdshell @ssisstr
    select @returncode

    there are two outputs from the syntaxes above:
    output1:
    Microsoft (R) SQL Server Execute Package Utility
    Version 9.00.3042.00 for 64-bit
    Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
    NULL
    Started: 11:38:36 AM
    Could not load package "F:dmacmdshell_test.dtsx" because of error 0xC0014049.
    Description: Cannot find folder "F:dmacmdshell_test.dtsx".
    Source:
    Started: 11:38:36 AM
    Finished: 11:38:36 AM
    Elapsed: 0.484 seconds
    NULL
    output2:
    5 --> return code is 5, which means the package could not be loaded

    1.What else do i have to check? i don't understand the error message because drive F is a local drive in a server. It is impossible that
    the engine cannot find the folder, the path is correct, though.
    2. Is the error happens because I still can't see the package in SSMS (Integration Services)?
    Need advice...thx in advance
  4. satya Moderator

    Is that F: drive a mapped drive?
    Are you saving the package with ENCRYPTION settings?
  5. adyseven New Member

    Hi Satya,
    1. No, just I told previously, drive F is a local drive in server, not a mapping one.
    2. No, I saved the package without encryption setting
    So, what should I do and check, next? please advice...
  6. satya Moderator

  7. adyseven New Member

    Hi Satya,
    Thx for the link. I've read it, good information though.
    Anyway, my problem is solved when I changed all my database connection in SSIS Designer using Windows Authentication instead of SQL Server Authentication. I don't understand why I can't using SQL Authentication to execute my package using syntax xp_cmdshell. Can someone give me explanation? Even when I used "sa" login, the xp_cmdshell is still unsuccessfully.
    Thanks in advance...
  8. satya Moderator

    May be you should check whether that SQL login used to execute this package has relevant permissions or not.

Share This Page