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

Reporting Services 2008 Parameter Dependencies & Choosing Multiple Parameter Values

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.

The AdventureWorks Sales Detail Report displays line-item sales details for a given product category, product subcategory and product. Our goal is to allow the end-user to specify the product category from the following available list (Accessories, Bikes, Clothing, Components). Once a Product Category choice is made, the available Product SubCategory choices for the chosen Product Categories are determined and displayed in the Product SubCategory drop-down list. In the screen snapshot below we chose Bikes and Clothing as our Product Category choices. For our Product Subcategory choices we chose Mountain Bikes (Bikes), Road Bikes (Bikes) and Caps (Clothing).

Notice that our Product Category is displayed along with the Product Subcategory in the following format: Product SubCategory (Product Category)

Our product subcategory choices are below:

Mountain Bikes (Bikes)

Touring Bikes (Bikes)

Caps (Clothing)

This was done just to tell the report viewer what category a subcategory belongs to. This was done because the subcategory / category relationship was not always obvious by name!

Parameters and Dependencies
Our sample report uses two parameters: Product Category and Product SubCategory. The subcategory choices are driven by or dependent upon the product category choice. In this section we’ll see exactly how this is done and also look at how we provide both the category and subcategory names to the end-user.

Product Category Parameter
The Product Category choices are provided by selecting the Name column form the ProductCategory table in the AdventureWorks2008 database. Below is the SQL code to provide the ProductCategory choices:

SELECT distinct
Name as ProductCategoryName
FROM
Production.ProductCategory

Notice that this parameter does not include any parameter dependencies since it is our initial choice. The Report Data window below allows us to define the parameter specifics such as the parameter name, parameter label, parameter data type, where the available values come from, what default value should be used, …

Below we see the Report Data window which allows us to create and fine-tune our parameters.

If we double-click on our highlighted parameter entry we see the Parameter Name, label and data type specified below. Notice that we also allow end-users to choose more than a single parameter value.

In the Available Values tab we specify how the parameter choices are driven by a query. Since this is the case, we specify the DataSet name that is used to provide the available Product Category values.

Product SubCategory Parameter

The Product SubCategory choices are provided by selecting both the Name column from the ProductCategory table and the Name column from the ProductSubCategory table both in the AdventureWorks2008 database. Using the SQL below, end-users are provided with a list of SubCategory names with the corresponding category name in the following format:

subcategory (category)

SELECT
PSC.Name + ' (' + PC.Name + ')' as productsubcategorynameandcategoryname,
PSC.Name as ProductSubCategoryName
FROM
Production.ProductSubCategory PSC
INNER JOIN
Production.ProductCategory PC
ON
PSC.ProductCategoryID = PC.ProductCategoryID
WHERE
PC.Name IN ( @ProductCategoryName)
GROUP BY
PSC.Name, PC.Name
ORDER BY
PC.Name, productsubcategorynameandcategoryname

Notice this SQL references the ProductCategory parameter using the ‘@’ parameter designator. (This entry was highlighted) This is like saying : “Provide us with Subcategory values given the product category choice that was already made and stored in the @ProductCategoryname parameter.” This is exactly how one can introduce parameter dependencies in reports -- your SQL code can reference one or more report parameters.

Extracting the Report Data

So far, we've looked at the SQL-code used to create parameter choices. The following SQL Code is used to extact the actual reporting data:

SELECT
P.Name as ProductName,
PC.Name as ProductCategoryName,
PSC.Name as ProductSubCategoryName,
SOD.OrderQty,
SOD.UnitPrice,
SOD.OrderQty * SOD.UnitPrice as LineTotal
FROM

Sales.SalesOrderHeader SOH
INNER JOIN
Sales.SalesOrderDetail SOD
ON
SOH.SalesOrderID = SOD.SalesOrderID
INNER JOIN
Production.Product P
ON
SOD.ProductID = P.ProductID
INNER JOIN
Production.ProductSubcategory PSC
ON
PSC.ProductSubcategoryID = P.ProductSubcategoryID
INNER JOIN
Production.ProductCategory PC
ON
PC.ProductCategoryID = PSC.ProductCategoryID
WHERE
PC.Name in ( @ProductCategoryName)
and PSC.Name + ' (' + PC.Name + ')' in

(@productsubcategorynameandcategoryname)
ORDER BY
ProductName,
ProductCategoryName,
ProductSubCategoryName

Notice in the highlighted section we are referencing both the Product Category and the Product SubCategory parameters. In fact, we are actually building the string that include the SubCategory Name and Category Name (i.e. Mountain Bikes (Bikes) ) so this filter matches the choice format the end-user is provided with.

Conclusion
It seems as though things have gotten easier in Reporting Services 2008. I’ve authored reports in both SQL Server 2000 and SQL Server 2005 and I remember using a Transact-SQL function (Split) to slice and dice values from a comma separate string.

SSIS Dynamic Data Driven Extracts

Extracting data from multiple tables: a dynamic SSIS Data-Driven Approach

This blog entry looks at how you can use SSIS to extract data from multiple tables leveraging a ‘source table’ that provides the table names to extract data from. This solution utilizes a For Each Container, Package Variables, Package Expressions applied to a package variabe along with an OLEDB Source whose SQL Extract Command is stored in a package variable.

In our sample scenario we receive sales data in tables from a 3’d party. This data is delivered on an unpredictable schedule and when received we’d rather not have to modify our SSIS solution. Instead, we rely on inserting a new row into the source table that tells SSIS what it needs to know about the new source table.

Our Source Table / data driven approach
Our dynamic approach involves the use of a source table to drive the SSIS Extract process and designate the tables to extract.

The Source Table SSIS utilizes is dbo.SalesSource and its schema is below:

CREATE TABLE [dbo].[SalesSource] (
[SalesSourceID] [int] IDENTITY(1,1) NOT NULL,
[SourceTableName] [varchar](64) NOT NULL,
[ExtractProcessedFlag] [int] default (0) NULL,
[ExtractProcessingOrder] [int] NULL,
[RowsExtracted] [int] NULL,
[DateTimeExtracted] [datetime] NULL
)

Initially, we have 2 source files to work with so, we start out inserting the following two rows into our dbo.SalesSource table (Notice we really only need to supply values for 3 columns). The SSIS package updates the ExtractProcessedFlag, RowsExtracted and DateTimeExtracted columns upon sucessful completion.

The ExtractProcessingOrder column allows us to specify the order in which we would like to extract data from in our Source Tables. The reason this was required was that our source data may very well contain duplicate data. We specify the order in which we extract and load and have our SSIS packages flag the duplicate rows based on a business key and keep only the most recent data from our duplicates. (In our scenario, new data trumps old data)

Let’s take a look at our SSIS Solution and then dissect each component:

The SSIS package uses the following Package Variables.

As we progress and dissect each SSIS task, we will look at each package variable it utilizes. The SSIS package uses a For Each container to cycle through each row present in the Source Table with the CurrentTable package variable holding the current table that needs to be extracted. Notice that we supplied an initial value for the CurrentTable. This way we can preview data in this table from the OLE DB Source connection we will see in the Data Flow task.

The package is fairly simple. It contains a highest level Sequence Container along with an inner For each Container. The For Each Container specifies an ADO Enumerator as its ‘Collection Type.’ The result set that feeds this container is below is produced by the Exec SQL Find Tables to Extract SSIS Task. The SQL Extract code is below:

select SourceTableName
from dbo.SalesSource
where ExtractProcessedFlag = 0
order by ExtractProcessingOrder

Notice that we are only extracting source tables where the ExractProcessedFlag is set to 0. The SSIS package sets this flag column to 1 when a source table is extracted. This EXEC SQL Task specifies the ResultSet Package Variable to ‘hold’ the results of the above select statement. (ResultSet is of type object)

The For Each Container Specifies a ‘For each ADO Enumerator’ as the Collection type and points the ResultSet package variable as the result set holder. ( Notice the setting for the Enumerator and the ADO object Source variable choice)

Also, the CurrentTable package variable is specified in the Variable Mappings tab. This allows our CurrentTable variable to essentially hold the current table being processed each time we cycle through the For Each loop. We'll see this package variable used in the expression that assigns a value to the SQLExtractCommand package variable.

The Data Flow task specifies that the SQL to extact data is present in a Package Variable. While this choice if probably not the most common Data Access Mode choice, it does provide us with the flexiblity we need. This package variable has its value dynamically set using Expressions. If you choose a package variable and display its properties (F4 will provide the properties) you will notice that by default the Evaluate As Expression property is False. This was changed to True as is seen below:

If you click on the the ellipse in the Expression value, you will be provided with the expression builder dialog box. The SQLExtactCommand’s contents are created using the quoted string along with the CurrentTable package variable as seen below. (Notice I did specify square brackets to surrount the table name since the table name may include non-alphabetical or other strange characters)

I found it helpful to click on Evaluate Expression and then paste the results into a Management Studio window. This way you can execute and validate the actual code.

The Data Flow Task that specifies the SQLExtractCommand package variable is below. You will notice that you are able to view columns and even preview data because we supplied an initial value for our CurrentTable package variable.

The final step in our SSIS package is a EXEC SQL statement that updates the source table to designate the SourceTable as being sucessfully processed.

UPDATE
dbo.SalesSource
SET
ExtractProcessedFlag = 1,
DateTimeExtracted = getdate(),
RowsExtracted = ?
WHERE
SourceTableName = ?


Parameters used in this update statement include the number of rows extracted from the source table along with the source table name.


Conclusion
What we left with is a SSIS package that extracts the tables to process from a source table. As new source tables arrive, we insert the rows into our source table without having to modify our SSIS package.

SQL 2008 SSMS Table Save Error

Using SQL Server Management Studio (SSMS) for SQL 2008, I had to make a change to one of my tables to add a column. However, I did not want to add the column at the end of the table, I wanted to add the column between two other columns. When I attempted to Save the table change, I had the following error appear:

In order to resolve this issue, I had to update the following setting in SSMS. Go to Tools-->Designers-->Table and Database Designers and uncheck the "Prevent saving changes that require table re-creation" checkbox: