none
The metadata could not be determined because statement 'REVERT... RRS feed

  • Question

  • Greetings. I have a 2016 SSIS Package SQL Server Native Client 11.0 connection. When attempting to have an OLE DB Command use this guy it throws up the following message when clicking on the columns tab"

    "The metadata could not be determined because statement 'REVERT        --Check if SSB is enabled in this database' in procedure 'sp_send_dbmail' does not support metadata discovery.". …..

    All it's attempting to do is the following:


    EXECUTE [msdb].[dbo].[sp_send_dbmail]
        @profile_name = 'CTDynamoAdmin'
        ,@recipients  = 'mylogin@myDomain.com'
        ,@body        = 'This is a test my custom e-mail body.'
        ,@subject     = 'Database Mail Test'

    I can run that same block of code from SSMS and the test email arrives. 

    Any ideas?


    Thanks in advance! ChrisRDBA

    Monday, October 7, 2019 3:06 PM

All replies

  • Hi Chris,

    Try now to set the resultset option to None and ensure nothing is mapped in the Resultset tab.


    Arthur

    MyBlog


    Twitter

    Monday, October 7, 2019 5:19 PM
    Moderator
  • That will work but we need a more permanent option as this is impacting us in many places. 

    Thanks in advance! ChrisRDBA

    Monday, October 7, 2019 5:27 PM
  • You should have used the SSIS built in Send Mail Task in the 1st place.

    You have no other options.


    Arthur

    MyBlog


    Twitter

    Monday, October 7, 2019 7:29 PM
    Moderator
  • This is one example of how we're getting this error. We may also throw other code before/ after this block of code. We may also just call a sproc that does several things, and also calls this code. The error pops up in those scenarios as well.

    Thanks in advance! ChrisRDBA

    Monday, October 7, 2019 10:17 PM
  • Hi ChrisRDBA,

    The OLE DB Command transformation runs an SQL statement for each row in a data flow.

    For example, you can run an SQL statement that inserts, updates, or deletes rows in a database table.

    It seems that OLE DB Command cannot execute the stored procedure,

    please refer to OLE DB Command Transformation in SSIS .

    If you want to execute the stored procedure ,please try to use Execute SQL Task .

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, October 8, 2019 2:42 AM