How to use Microsoft Sql Server as source/destination in SAS Data Integration Studio

[lfabbri]

Hello everyone, I need to write pipeline jobs in SAS Data Integration Studio with connections to Microsoft Sql Server databases both as source and destination for the data.

From the documentation I read that there is the possibility to setup an ODBC connection, but it must be created at operative system level (using microsoft windows odbc administrator). Is this the preferred way to setup connections to MSS in SAS 9.4 or there is a better way?

Thanks for any information!

[JuanS_OCS]

Hello @lfabbri,

it depends on the SAS/ACCESS software your company has purchased. You can know this info by running proc setinit;run;

In general, you can connect on several ways: ODBC, OLEDB and with the native MSSQL connection.

Normally, the MSSQL connections would be faster and they have the potential to run even in-database procedures if you have the license/software.

[lfabbri]

Hi @JuanS_OCS, thanks for the reply.

I've run the comand, this is the output:

---Base SAS Software
  30JUN2019
---SAS/STAT
  30JUN2019
---SAS/GRAPH
  30JUN2019
---SAS/CONNECT
  30JUN2019
---SAS Enterprise Miner
  30JUN2019
---SAS Integration Technologies
  30JUN2019
---SAS/Secure 168-bit
  30JUN2019
---SAS/Secure Windows
  30JUN2019
---SAS Enterprise Miner Server
  30JUN2019
---SAS Enterprise Miner Client
  30JUN2019
---SAS Text Miner
  30JUN2019
---SAS Enterprise Guide
  30JUN2019
---SAS/ACCESS Interface to PC Files
  30JUN2019
---SAS/ACCESS Interface to ODBC
  30JUN2019
---SAS/ACCESS Interface to MySQL
  30JUN2019
---SAS Metadata Bridges for General Industry Standards
  30JUN2019
---SAS Workspace Server for Local Access
  30JUN2019
---SAS/ACCESS to Postgres
  30JUN2019
---High Performance Suite
  30JUN2019
---SAS Add-in for Microsoft Excel
  30JUN2019

Could you please point me where I can find a detailed guide/docs on how to properly setup the connection using the native MSSQL interface (I guess it is supported in my configuration)? There is a tons of documentation and I am a bit lost.

Thank you very much!

[JuanS_OCS]

Hi @lfabbri,

if you do now have the ODBC connections in your OS, you can always define them on code:

http://support.sas.com/kb/52/777.html

If you want to use pass-through: http://support.sas.com/kb/41/888.html

I recommend you a general read of this short document: http://support.sas.com/techsup/technote/accessing-microsoft-sql-server-from-sas.pdf

[LinusH]

Well, you always need a driver to be installed.

But if you are asking how to do a connection without having to define a DSN, see this thread:

https://communities.sas.com/t5/SAS-Data-Management/Example-of-DSN-less-SQL-Server-connection-from-Li...

[lfabbri]

Thank you, I am asking because when I register a server or a library in Management Console I can see both ODBC server and Microsoft SQL Server options, although in the documentation I've read so far only the ODBC option is described.

[LinusH]

So I'm not sure what the question is?

There are several guides on how to set it up in SMC for ODBC sources.

If I recall right, you first select ODBC, but I then still specify which the underlying source is.

[SASKiwi]

If you are creating ODBC connections to SQL Server then you define the server in SMC as an ODBC server.

[lfabbri]

Thanks guys for the great help, I was able to create the odbc server and library.

Now I have the following problem

The odbc library I've created is visible in Data Integration Studio by me and other users. I've also registered some tables from the SQL Server database, which are visible as well in the Inventory by me and other users. The problem is when creating a job that involves read/write from that odbc connection/library, it works only for my user but it fails for the other users with the following errors:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

ERROR: Error in the LIBNAME statement.

ERROR: Libref DWHANPAL is not assigned.

I need to make this odbc library working for all the sas users. What I have to do?

Thanks in advance!

[JuanS_OCS]

Hello @lfabbri,

please share with us your code (I suggest masking your sensible data of the connection), and please be aware, on the server where you run the SAS code, you still will need the correct ODBC to SQL Server (and right version) to be at least installed as bare minimum.

[lfabbri]

Hi, I've finally solved creating a System DSN instead of a User DSN for my ODBC connection

[ccaulkins912]

@JuanS_OCS did @lfabbri ever get to show you code/solution for his SQL Server issue?

[JuanS_OCS]

Nope, @ccaulkins912 ! Although it would be quite nice 🙂