SSIS Hresult: 0x80004005 Consider using the WITH RESULT SETS clause to explicitly describe the result set.SSIS Hresult: 0x80004005

I was trying to run a stored procedure in SSIS and put the results into a table and got this error:

Error at Data Flow Task [Call sp_Get_Activity [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft OLE DB Driver for SQL Server” Hresult: 0x80004005 Description: “The metadata could not be
determined because statement ‘EXEC (@TSQL) at Production’ in procedure ‘sp_Get_Activity’ contains dynamic SQL.
Consider using the WITH RESULT SETS clause to explicitly describe the result set.”

Keep reading to see how I fixed it!

I have this stored procedure. Pretty simple, it just queries the database and returns one row.

Now I want to run this stored procedure in SSIS as part of a larger workflow and store the results in a table. When I add it to the package to run the command, the lovely error message shows up.

Unlike some SSIS errors, this one is helpful. The last sentence gives us the solution: WITH RESULT SETS.

Doing a quick search through the documentation: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql?view=sql-server-ver15

We just need to define what columns are being returned by the procedure and provide a datatype for the data. The most important part is the double parenthesis around the column names and datatype….see below in the example. If you only have one parenthesis, you will get an error message.

After adding the with result sets, we can now press OK without getting an error.

Now we can add a destination location and map the fields just like normal.

Now we can run the SSIS package. Success! Green check marks are good.

The table is now populated with data from the stored procedure.

As far as error messages from SSIS, this one was very helpful and we were able to solve our issue.