Category Archives: Working with Oracle

Connecting to Oracle from SQL Server. Working with PL/SQL.

Getting information from Oracle into SQL Server using SSIS

  By | Working with Oracle
Leave a comment

I needed to populate a SQL Server database with information from an Oracle database, but found when I created a linked server and ran the INSERT statement manually, I was getting errors regarding truncation and datatypes.  I had already run this successfully using the IMPORT data wizard (and had my application owner confirm the data) so was a bit confused.  SSMS wasn’t reporting which row or column was problematic.  Overall, my goal was to run a nightly job to run the INSERT statement, but this wasn’t working.

Ultimately, I found that during the IMPORT data wizard, I could choose to save the process as an SSIS package, in the MSDB database.  I chose this option, created a job to call the package and was off and running.

This was great, but what if there were problems during the data move?  Doing some research, I had come across this outlining logging in SSIS packages.  I exported my existing package to the file system, opened it up in BIDS and gave it some logging options.  I renamed it, imported it back into MSDB and altered the job to call the new package.

How to export a package from the MSDB database:

Open SSMS, choose “Integration Services” for “Server Type:”.  Enter your SSIS credentials.

In the “Object Explorer” pane under “Integration Services”, expand “Stored Packages” -> MSDB.

Right click the package to export, choose a location on the file system and name for the package.

To edit this package in BIDS:

Open BIDS, choose “File” -> “Open” -> “File.  Locate and choose the freshly exported package.

To add logging to a file for this package:

In BIDS, choose “SSIS” -> “Logging”.

Choose the “Provider Type” -> “SSIS log provider for Text Files”.  This will populate the grid below.

Click on the “Configuration” field, a drop down will appear.  Choose “New Connection”.

A new window will open, you can choose to log to a new file, existing file, new folder or existing folder.  I chose a new file each time and put it in my SQL Server log directory (…\MSSQL.1\MSSQL\Log)

Create a linked server to an Oracle database (and not lock up the instance)

  By | Random errors and weird experiences, SQL Server w/o the GUI, Working with Oracle
6 Comments

I’ve created numerous linked servers to Oracle from SQL Server and what should be a straightforward process using SSMS has caused me some headaches.  For whatever reason, when using the GUI, I’ve actually locked up the entire SQL Server instance and had to restart the service when trying to create a linked server to Oracle.  This is not good when working in production. The following will outline the do’s and don’ts for this process.

To begin: download, install and configure the Oracle client to communicate with your Oracle database.  This link can assist you with that process.

Once the Oracle client is installed, here’s the steps that can cause potential problems (skip to code section for the correct solution):

Using SQL Server 2005 Management Studio, I expanded ‘Server Objects‘, right clicked ‘Linked Servers‘ and chose ‘New Linked Server‘.  I entered a name in the ‘Linked Server‘ text box then went to ‘Provider‘ (after choosing the ‘Other Data Source‘ radio button).  The drop down showed me a list of the drivers installed; to connect to Oracle we have two choices: the Microsoft driver, called ‘Microsoft OLE DB Provider for Oracle‘ and the Oracle driver, ‘Oracle Provider for OLE DB‘.  Here’s where I ran into problems: if I chose the Oracle driver, about half the time the drop down would freeze and eventually the instance would become unresponsive.  I’d have to restart the SQL Server service.

Here’s the code section, showing how to create a linked server using T-SQL:

-- Create the linked server
EXEC master.dbo.sp_addlinkedserver
@server = N'LinkToOracle', -- name of the Linked Server
@srvproduct=N'Oracle',
@provider=N'MSDAORA', -- Microsoft driver
@datasrc=N'myOracleDatabase' -- TNS name of the Oracle database
go
 
-- Add the user to connect to the Oracle database
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'LinkToOracle', -- name of the Linked Server
@useself=N'False',
@locallogin=NULL,
@rmtuser=N'myOracleUser', -- Oracle database user
@rmtpassword='########' -- Enter the correct password here
go

The above code will create a linked server called ‘LinkToOracle’ using the Microsoft OLE DB driver, then using an Oracle database user to authenticate.  To use the Oracle OLE DB driver, replace the ‘@provider’ line in the top call to the sp_addlinkedserver stored procedure with:

@provider=N'OraOLEDB.Oracle'

To test connectivity, open a query window and do a select from a table that the Oracle user has access to.  The following assumes an ‘Employee’ table in the ‘HumanResources’ schema exists in the Oracle database:

SELECT count(*) FROM LinkToOracle..HumanResources.Employee

Notice the double dots between ‘LinkToOracle‘ and ‘HumanResources‘.  This denotes use of the default catalog of the linked server.

To send the query to the Oracle database for processing, which would allow you to write PL/SQL, use the OPENQUERY command:

SELECT * 
FROM OPENQUERY(LinkToOracle, 'select count(*) from HumanResources.Employees')