Post reply

Errors caused by nested calls of xp_cmdshell

  • June 6, 2017 at 7:34 am

    #343100

    Hi,

    I have SISS package with data flow task (OLE DB Source -> Data conversion transformation -> Flat file destination) for SQL Sever 2014. OLE DB source is calling my custom SQL procedure. When I call this procedure directly from management studio it works well but when is called by JOB with proxy account it finish with error. I found that problem is caused by nested calls of xp_cmdshell and can be solved by adding WITH RESULT SET but in my case I don't know how to write it in right way. For example in my procedure I have something like this:


    SET @cmd = 'dir '+ @destination_filepath + '\ /b /ad' 
    INSERT INTO #dir_list EXEC xp_cmdshell @cmd 

    And this is my not working version with WITH RESULT SETS

    SET @cmd = 'dir '+ @destination_filepath + '\ /b /ad' 
    INSERT INTO #dir_list EXEC xp_cmdshell @cmd WITH RESULT SETS ( (output NVARCHAR(255)) )

    And here is error:
    Started: 12:16:42
    Error: 2017-06-06 12:16:42.35
     Code: 0xC0202009
     Source: Data Flow Task 1 SQL Server [19]
     Description: 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 'INSERT INTO #dir_list EXEC xp_cmdshell @cmd' in procedure 'spXML_EXPORT' uses a temp table.".

    What is the right way to solve this problem? Thank you very much for any help.

  • June 6, 2017 at 7:55 am

    #1945603

    what specific error are you getting?
    I would suspect that the issue is you are using a temp table instead of a permanent table,and unless you've got the setting "RetainSameConnection" = true on your Connection Manager, the temp table falls out of scope False and is destroyed , and does not exist when the SSIS package moves to the next step.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 6, 2017 at 8:13 am

    #1945609

    Here is full error log. My job has only one step but SSIS data flow has 3 blocks mentioned in previous post.

    Started: 12:16:42
    Error: 2017-06-06 12:16:42.35
     Code: 0xC0202009
     Source: Data Flow Task 1 SQL Server [19]
     Description: 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 'INSERT INTO #dir_list EXEC xp_cmdshell @cmd' in procedure 'spEXPORT_XML' uses a temp table.".
    End Error
    Error: 2017-06-06 12:16:42.35
     Code: 0xC020204A
     Source: Data Flow Task 1 SQL Server [19]
     Description: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
    End Error
    Error: 2017-06-06 12:16:42.35
     Code: 0xC004706B
     Source: Data Flow Task 1 SSIS.Pipeline
     Description: "SQL Server" failed validation and returned validation status "VS_ISBROKEN".
    End Error
    Error: 2017-06-06 12:16:42.35
     Code: 0xC004700C
     Source: Data Flow Task 1 SSIS.Pipeline
     Description: One or more component failed validation.
    End Error
    Error: 2017-06-06 12:16:42.35
     Code: 0xC0024107
     Source: Data Flow Task 1
     Description: There were errors during task validation.
    End Error
    DTExec: The package execution returned DTSER_FAILURE (1).
    Started: 12:16:42
    Finished: 12:16:42
    Elapsed: 0.281 seconds

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

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