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!

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

SSIS: The Script Task “xxxx” uses version 15.0 script that is not supported in this release of Integration Services

What is this error message and how do you fix it? I had a script task in one of my SSIS packages and when I deployed it and tried to run it I received this message:

Load multiple Excel Tabs:Error: There was an exception while loading Script Task from XML: System.Exception:
The Script Task "ST_9a61fb4231de4386a89bc7783e2f024b" uses version 15.0 script that is not supported in this release of Integration Services.
To run the package, use the Script Task to create a new VSTA script. In most cases, scripts are converted automatically to use a
supported version, when you open a SQL Server Integration Services package in %SQL_PRODUCT_SHORT_NAME% Integration Services.
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events)

Continue reading SSIS: The Script Task “xxxx” uses version 15.0 script that is not supported in this release of Integration Services

Connect SSIS to MS Access ACCDB databases

Before Microsoft changed Access from .mdb file extensions to .accdb, SSIS would work right out of the box connecting to Access databases.  Due to the new format, you now need to download additional drivers on any machine that runs the SSIS jobs.  I am going to show you how to do it right now.

Continue reading Connect SSIS to MS Access ACCDB databases

SSIS Add Filename During File Load

When loading files into staging tables using SSIS, it is often helpful to include the filename during the load so you can verify where the data came from.  This is easily accomplished with a buried option which in my opinion should be better spotlighted called the Advanced Editor for modifying flat file sources.

Continue reading SSIS Add Filename During File Load

SSIS Conditional Split Transformation

You may have a big file or a large database table that you want to split up into different csv files.  Instead of writing multiple queries for the different cases, you can use  Conditional Split within SSIS to accomplish this task.  There are also a multitude of other scenarios you can use it for.

Continue reading SSIS Conditional Split Transformation

SSIS ORA-00900: invalid SQL statement calling Oracle Procedure

Calling an Oracle stored procedure in SSIS is a little different than calling a SQL Server version.  Today I will show you the error you get if trying the SQL Server syntax and how to fix it.

Continue reading SSIS ORA-00900: invalid SQL statement calling Oracle Procedure

Load multiple files with SSIS Foreach Loop Container

You just finished your SSIS project where you needed to load a CSV file into a Sql Server database table, but now you just found out that multiple files with the same data will now need to be loaded.  Using the Foreach Loop Container in SSIS makes this relatively easy.

Continue reading Load multiple files with SSIS Foreach Loop Container