RDA Corp - Business Intelligence and SQL Server

« July 2009 | Main | September 2009 »

August 24, 2009

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

SSIS Data Flow Task – OleDBCommand Task on ORACLE database throwing “Provider cannot derive parameter information” error

Problem
We were trying to update a table in Oracle database within a dataflow task. OleDbCommand transformation task is used for the purpose. The update statement takes some parameters. SSIS could not resolve the parameter information and throw error “Provider cannot derive parameter information”. We are using SSIS 2005 and Oracle 10G .NET drivers.

Analysis
Typically, OleDbCommand transformation task automatically resolves the parameters based on the SQL statement. For example, let’s try updating a same table in SQL Server database and Oracle database

SQL Statement: UPDATE Employee SET Bonus = ? WHERE EmpID = ?

With SQL Server
Set the SQL Server connection in “Connection Managers” tab. Fill the SQL statement in the “Component Properties” tab as shown below.


Figure 1 - Adding SQL Statement to OleDbCommand Task

If you check the “Column Mapings” tab in the OleDBCommand transformation task, you would see the parameters list as follows. SSIS automatically resolves the parameters needed. Map the columns appropriately and the Data Transformation task is ready


Figure 2 - Parameters List (SSIS automatically resolved)


With Oracle
When the same SQL is tried on Oracle database, SSIS throws the following exception. SSIS cannot figure out the parameters automatically.

Validation error. Update Oracle Table: UPDATE Employee [60]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E51. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E51 Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
Validation error. Update Oracle Table: UPDATE Employee [60]: Unable to retrieve destination column descriptions from the parameters of the SQL command.


Figure 3 - SSIS failed to resolve the parameters with Oracle database

Solution
Do not know if this is bug in SSIS. But one way to resolve is to add the parameters manually. Go to the “Input and Output Properties” tab and add the parameters manually. Add the parameters with the appropriate data type and in the same order as they appear in the SQL statement. This resolves the error and you can map the input columns to parameters.



Figure 4 - Adding parameters manually


Figure 5 - Parameters added manually


Figure 6 - Map the parameters to Input columns

Share:  

Posted by Steve Mann at 8:20 AM | Comments (0)

August 21, 2009

Reporting Services 2008 Parameter Dependencies

As was the case with prior versions, Reporting Services 2008 allows you to establish parameter dependencies and to allow mutiple parameter values be chosen. This means that parameter values provided to the end user can be dependent upon or driven by prior parameter value choices. In this example we’ll see how this can be done using a very simple AdventureWorks2008 Sales Data example report.

Share:  

Posted by Steve Mann at 1:04 PM | Comments (0)

Reporting Services 2008 Parameter Dependencies

As was the case with prior versions, Reporting Services 2008 allows you to establish parameter dependencies and to allow mutiple parameter values be chosen. This means that parameter values provided to the end user can be dependent upon or driven by prior parameter value choices. In this example we’ll see how this can be done using a very simple AdventureWorks2008 Sales Data example report.

Share:  

Posted by Steve Mann at 1:04 PM | Comments (0)

Reporting Services 2008 Parameter Dependencies

As was the case with prior versions, Reporting Services 2008 allows you to establish parameter dependencies and to allow mutiple parameter values be chosen. This means that parameter values provided to the end user can be dependent upon or driven by prior parameter value choices. In this example we’ll see how this can be done using a very simple AdventureWorks2008 Sales Data example report.