Post reply

SSIS Metadata can not be determined

  • July 13, 2016 at 4:11 pm

    #328203

    Hello,

    I have upgraded ssis package from 2005 to 2014.

    I have this component inside package "Executes an SQL command for each row in a dataset." and I am using a SP called EXEC sp_exe_cmdshell_restore ?

    I am getting validation error for this component and it says,

    Error1Validation error. generate sql command 1: generate sql command 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "The metadata could not be determined because statement 'EXEC @Result = xp_cmdshell @sqlcmd ; -- parse list of dumps to get db name for load' in procedure 'sp_exe_cmdshell_restorel' invokes an extended stored procedure.". dr_load_database.dtsx00

    This is my SP

    ALTER PROCEDURE [dbo].[sp_exe_cmdshell_restore]

    @sqlcmd nvarchar(1000)

    AS

    BEGIN

    SET NOCOUNT ON;

    DECLARE @Result int

    EXEC @Result = xp_cmdshell @sqlcmd ;

    END

    I am trying to restore few databases automatically daily. So @SQLCMD is grabing various restore db commands from above processed text file. These statements looks such as

    "restore database @dbname from ''\\share\db_path\backup_today's date.bak"

    And then EXEC sp_exe_cmdshell_restore ? is processing each statement to restore all these databases.

    This is working in 2005 but in 2014 I am getting this validation error.

    Please let me know how to troubleshoot this.

    Thanks.

  • July 13, 2016 at 5:33 pm

    #1889992

    Check out

    Exec [sp] with result sets

    in BOL.

    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

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

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