SSIS issues previewing source data

Question

question

jeremy1216 asked Nov 30, '18

SSIS issues previewing source data

I have SSIS packages to import data from a Oracle database. Connection to source are ok and the parsing the query reveal no issue. However, when clicking the preview button it comes the error message. Any idea what's causing that?

=================================== There was an error displaying the preview. (Microsoft Visual Studio) =================================== The system cannot find message text for message number 0x80040e51 in the message file for OraOLEDB. (OraOLEDB) ------------------------------ Program Location: at Microsoft.DataTransformationServices.Design.UnsafeNativeMethods.ICommandWithParameters.GetParameterInfo(IntPtr& pcParams, IntPtr& prgParamInfo, IntPtr& ppNamesBuffer) at Microsoft.DataTransformationServices.Design.DesignUtils.GetQueryParameters(ConnectionManager connectionManager, String sqlStatement) at Microsoft.DataTransformationServices.Design.PipelineUtils.ShowDataPreview(String sqlStatement, QueryParameter[] parameters, ConnectionManager connectionManager, Control parentWindow, IServiceProvider serviceProvider, IDTSExternalMetadataColumnCollection100 externalColumns, Int32 timeout) at Microsoft.DataTransformationServices.DataFlowUI.DataFlowConnectionPage.previewButton_Click(Object sender, EventArgs e)

ssis error

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2023-06-10T10:21:56Z

Chris.Golledge answered Jun 10, '23

I enabled tracing.

So, the trace identifies the error as 0x80040e51, which we already knew. Looking up the error from Oracle, "Provider cannot derive parameter information and SetParameterInfo has not been called." This is true; the trace shows that SetParameterInfo has not been called.

Background

Because most DBMS support some forms of implicit or automatic casting (like from string to number), the relationship between client parameter data type and server data type is many:1. If the client app has not told the provider what parameter type to expect, the provider kind of has to guess.

Hypothesis

My best guess is that Microsoft decided to be developer-friendly and if the app has not told the provider what type to expect, it will prepare the query and use the database type to infer a best fit application parameter type. Oracle decided that it was not going to guess and requires the application to tell it what is the type of the parameter it is going to pass in.

That would explain why this issue has remained unresolved for years and years, through 32-bit and 64-bit. (Probably even 16-bit; OLE DB is that old.)

To test this hypothesis, I created a source that is an SQL Command and clicked Preview:

SELECT *
FROM dual

There are no parameters; yet the exact same stack trace and error results are returned. So, very likely SSDT is coded to expect SQL Server behavior and always calls GetParameterInfo().
The error advice is:

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

I will be surprised if we can find a way to make Preview work with Oracle and OLE DB.I did get Preview to work with an ADO.Net source, but that does not allow up to use an UPDATE statement in a command object.

There was a defect logged at Microsoft on this, but the only reference I found is in an old, archived forum. https://social.msdn.microsoft.com/Forums/en-US/892e1cb0-9090-4406-8601-52b3a3a4fc11/unable-to-preview-oracle-ole-db-source-in-ssdt-172vs2015?forum=ssdt

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question