Errors on oledb command tasks in data flow when I change providers on connection manager from oledb to sqlncl 11.1?
I have over 1 thousand etl packages that use and OLE DB Command task in the data flows. I have just update the connection manager for those OLE DB Command tasks from an OLEDB provider to a SQLNCL 11.1 provider. I am now getting the following error from the OLE DB Command tasks: Error: 0xC0202009 at Merges Data, Inserts Update Error Details : 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 sp_executesql @Statement, @ParamDef, @vcNT=@vNext_Seq OUTPUT' in procedure 'usp_Create_Child_Parent_Seq' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.". Error: 0xC0202080 at Merges Data, Inserts Update Error Details : Unable to retrieve destination column descriptions from the parameters of the SQL command. Error: 0xC004700D at Merges Data, Inserts Update Error Details : The layout and one or more components failed validation. Error: 0xC004701A at Merges Data, SSIS.Pipeline: Inserts Update Error Details failed the pre-execute phase and returned error code 0xC004700D. Is there an easy fix for this? If I move the provider back to the OLEDB provider, will I have any issues with SQL Server 2016 or Visual Studio 2015 and future versions?