ds-blog-icon.png

THE DATA DOWNLOAD

As a business intelligence consulting company, we pride ourselves on being able
to deliver on our projects as well as provide good quality content to our readers.

Informatica Linux on SQL Server 2012

Posted by Tom Nats on Dec 20, 2016 10:10:44 AM

By: Tom Nats

With SQL Server 2012, Microsoft has included a much needed ODBC driver for Linux.

iStock_000018336050XXXLarge_EDITED.png

I was attempting to connect to a SQL Server 2012 database using PowerCenter 9.5.1 HF1 and I was getting special characters. (it looked like Japanese or Chinese) I set up a new ODBC connection and pointed to the following driver: PowerCenter9.5.1/ODBC7.0/lib/DWmsss26.so

It connected but the columns that were nvarchar had the special characters:

sqlserver2012

I couldn't find much on the Informatica KB or forums so I decided to try out the new Linux driver from Microsoft.
1. Download and install unixODBC. Before you run the ./install.sh install step go to #2 below
http://blogs.technet.com/b/sqlman/archive/2012/08/03/step-by-step-setting-up-linux-odbc-driver-for-sql-server.aspx
then

2. unixODBC Driver Manager
http://technet.microsoft.com/en-us/library/hh568449.aspx

3. Run: install.sh install
Here is the entry in the odbc.ini file:
[AW]
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0
Server=10.230.1.123
Port=1433
Database=AdventureWorks2012
AnsiNPW=Yes

Here is the data after using the Microsoft Linux ODBC driver:
sqlserver2012_linux

I haven't tested this driver out much more than my testing but it appears this would be the way to connect to the newer SQL Server version vs. the supplied Infa one from DataDirect.

*update* The correct Data Direct driver to use for SQL Server 2012 is: DWsqlsxx.so (Thanks Srinivas Sampath with Informatica!)
Example odbc.ini: Driver=/Informatica/ODBC6.1/lib/DWsqls25.so
See KB 122672
The question is which driver will perform better and support most features? Maybe a benchmark would be in order here...

Are you ready to discuss your project?

Let's chat about it, we look forward to helping and becoming your data partner. 

request-call-with-practice-lead

 

Topics: Tool Selection, Informatica, Blog, Informatica PowerCenter

Written by Tom Nats