SSIS – Creating an OLEDB Connection to an ODBC Source and SSIS parameters

This post was originally going to be about using the advanced features of a SSIS OLEDB command to set parameters.  I’ve been using Oracle sources lately and the OLEDB command did not recognise the parameters for my query. 

For example, my in my work environment, the statement (below) just did not recognise the key_field parameter and so I had to manually add it through the components advanced editor.

Update some_table 
set some_field = new_value 
where key_field = ?

We’ll look into the advanced features a little later, however, when I tried to mimic the behaviour (using a MYSQL connection), the only issue I had was creating an OLEDB connection in SSIS.  I have ODBC connections on my machine however, (naturally enough given the components name), the OLEDB command requires an OLEDB connection.

So, to create an OLEDB command (and have it reference an ODBC source), simply use the Native OLE DB\MSDataShape provider and use the ODBC name as the server (as in the image below).  The UserId, Password and catalogue/schema can all be set through the connection managers interface.



Parameter Management under OLEDB

I can now use that OLEDB connection to execute an OLEDB Command.  As discussed above, my original intent was to show how to add a parameter when SSIS did not recognise the parameters.  In my situation, this was against an Oracle source.  In this demo, I’ll use a simple command

update adventure_works_2012.customer 
set territory_id = ? 
where account_number = ? 

and the column mappings tab of the OLEDB command allows me to map fields in my source (input data flow) to my command (ie parameters in the destination).  We can see this in the mapping screen shot below.


You will notice that the names are generic (Param_0 …. ) and refer the the placement of the ? symbols in the source query.  That’s not really user friendly and if i want to give these meaningful names, you would think that I can set them in the Input and Output Properties (using the Name Property as below).  However, this is not the way this UI works and the name defaults to the placement of the ? in the query.  Long story short, you can’t control the names of the parameters.


However, this part of the (advanced UI) does give us a little indication into how to add parameters.  If no parameters are recognised, my can add them through the ‘Add Column’ button on the External Columns node of the Inputs and Outputs tab.  This is what i had to do with my Oracle command.


SSRS Report Parameters from Unfriendly Sources

We have recently developed some SSRS reports against Non SQL Server sources and have run into a few issues regarding the use of multi-value parameters.  Since, we are sure to meet this problem again (and variations of it), this post is just as much of a bookmark for ways around the problems that we’ve encountered.

So what’s the problem?  Lets assume were using an  MySql Source with the same data as AventureWorks, we can to pick a Subcategory and then have our report show a list of products.  This is pretty straight forward.  The products query uses a ? to identify a parameter, and that parameter is assigned a value to in the dataset properties;

SELECT        *
FROM            adventure_works_2012.product
WHERE        (ProductSubCategoryID = ?)

You can see how this is setup in the image below.  The SubCategory dataset is the source for the SubCategory parameter and that is used as a parameter for the Products query.


Unfortunately, this only works if our SubCategory Parameter DOESNOT allow multiple values.  If we enable this as an option for the Parameter, we’ll get an error like the following when we try to view the report.


To make the products query run with multiple parameter values we need to dynamically generate the SQL associated with the parameter.  Actually there are a couple variations of this however they all use the same theme.

So, first, we change our query to recognise the need for multiple values from the parameter;

SELECT     *
FROM            adventure_works_2012.product
WHERE        ProductSubCategoryID in (?)

Then, instead of supplying the parameter value to the dataset, we use an expression that delimits parameter values.


Here, the source type is a integer so we are expecting something like 1,2,3 etc to be passed as the parameter’s value.  If the source type was a string, we’d just have to append quotes around the statement.

= Join(Parameters!SubCategory.Value, "’ ,’")


We can use a few variations of this approach to generate the parameter list.  For example, we could exclude the parameter definition from the Products dataset and specify the query as a function that returns an SQL string.  Using this approach, the Parameter value is still called but the parameter object is not defined in the dataset.

We can also define a some VB code that returns the SQL string (I find this a little more flexible).  Here the string value of the parameter is passed to the VB function.  So here, I’ve created a simple function and added it to the report;

Public Function SQL(ByVal Keys as String) as String
  return “select * from adventure_works_2012.product where ProductSubCategoryID in (” & Keys & “)”
End Function

Now, our dataset is defined by the expression

=Code.SQL( Join(Parameters!SubCategory.Value, ",") )

Oracle Batches & Vairables

More of a prompt for me than an informative post 🙂

Oracle params/variables can be defined in a batch statement through a variable keyword and then assigned a value using the exec (execute) command.  For example, we can define 2 variables and give them a value with the statements below (note the use of := for assignment)

variable v1 nvarchar2(20);
variable v2 nvarchar2(20);
variable v3 nvarchar2(20);

exec :v1 := 'Val 1';
exec :v2 := 'Val 2';
exec :v3 := 'Val 3';

select :v1 as v1, :v3 as v2,
from dual;

select :v2 as v2 , :v1 as v1
from dual;

When executing the command (I’ve tried this on TOAD and SQL Developer), the function key used with either

  1. Execute the batch  (if F5 is pressed) or
  2. Execute the current command where the cursor is (if F9 is pressed).  This relates to the statement surrounding the cursor position.

Where the command is executed (F9), any variables in the command are prompted for entry (as shown below).