Power BI report containing a column chart with small multiples that show custom labels on each column.
Notice the label on each column that shows the year and average game length in h:mm format.
These custom labels are available for any bar or column chart – small multiples are not required.
How to add custom labels
The setting for these labels is a little bit hidden. On my column chart, I went to Format Pane, located the Data labels section, and found the Values area. Under Values, there is a toggle for Custom label that must first be enabled before populating the Field below it.
You can drag any field in the fields list into the custom label field. It only accepts one field, so if you want to display multiple values, you’ll need to create a custom column to concatenate values. That is exactly what I did in my example report.
You can control the orientation and position of the labels (mine are set to horizontal and inside base). You can also format the label values and background.
I recently worked with a client who was upgrading and deploying several SSIS projects to a new server. The SSIS packages connected to an Oracle database in various tasks. There were:
Data Flows that used the Oracle Source and Oracle Destination
Data Flows that used an OLE DB connection to Oracle
Execute SQL tasks that connected to Oracle via an ADO.Net connector
We needed to make sure we had the most current version of the drivers required to make the package work.
This turned out to be an adventure involving 3 different drivers and plans to refactor, so I’m documenting some of it here in case it helps someone else.
SSIS Target Versions and Visual Studio Versions
It’s important to understand the relationship between Visual Studio and SSIS target versions. In Visual Studio, we must install an extension to allow us to develop SSIS packages. The version of Visual Studio is different but related to the SSIS target version. Visual Studio 2019 with the 2019 SSIS extension supports SSIS versions 2012 through 2022. Visual Studio 2022 with the 2022 SSIS extension supports SSIS versions 2017 through 2022.
The target version is set in the project configuration properties. This target version should match the version of SSIS running on the server where you plan to deploy the SSIS packages.
Oracle Driver Changes since SSIS 2017
In the olden days (pre-SQL 2019) we used the Attunity Connector to connect to Oracle because it provided the best performance. Attunity was acquired by Qlik in 2019, and Microsoft took over maintaining and distributing the connectors. If you need the Attunity connectors for SSIS versions 2012 – 2017, you will find the links for the downloads here.
For target SQL Server versions 2019 through 2022, we now have the Microsoft Connector for Oracle. This new connector is interesting because we are no longer required to install an Oracle client to make it work. It also allows us to connect directly to an Oracle server without having to use a TNSNAMES.ORA file if we so choose, but the TNSNames file can still be used if desired.
There are separate downloads of the Microsoft Connector for Oracle for SQL Server 2019 and 2022. And you have to download the 32-bit driver and 64-bit driver separately.
Different Drivers for Different Connection Types
Attunity wasn’t the only way to connect to Oracle using SSIS. As noted above, you could also use a .Net provider for Oracle or an OLE DB provider for Oracle. To reduce complexity and increase supportability, I would advise you that you probably want to consolidate to use a single driver to connect to Oracle as much as possible. But note that the Microsoft Connector for Oracle cannot be used in an Execute SQL task, so you might still end up with two different connectors without a lot of refactoring.
This gets a bit confusing due to naming, so I’m linking to the current version of drivers (as of May 2023) to be used with SSIS target version 2022. These are the drivers I’m aware of that I’m sure are supported by either Microsoft or Oracle. There are some other drivers out there that are either no longer maintained or provided by third parties.
You’ll need to install both the 32-bit and 64-bit drivers. The 32-bit is used in Visual Studio when developing SSIS packages. The 64-bit driver is used by default when executing packages on the SSIS server (unless you configure the package to execute in 32-bit mode).
In SSIS 2022, if you are reviewing SSIS logs and see errors related to an Oracle connection, and you see mention of ODBC and the Oracle Connection Manager, that is the Microsoft Connector for Oracle.
If you see errors in the SSIS logs related to an Oracle connection, and you see mention of OLE DB provider OraOLEDB.Oracle.1, that is the driver from ODAC.
I hope this makes clear your options and required drivers for connecting SSIS to Oracle.
Data Savvy 