Importing a .SAS7BDAT file into SQL Server Using SSIS

I was tasked with importing a .SAS7BDAT file into SQL Server the other day, and there were enough non-intuitive pieces to it that I thought I'd write down the steps while they still were fresh.

Some searching shows it's a problem with several solutions, but most of what shows up seems unnecessarily complicated, at least, for someone who's used to using Integration Services.  It turns out that there's a very easy method for accessing the data in one of these files, and it doesn't require a SAS client.  It does require a download from SAS, but that is freely available.

Download and Install the SAS OLEDB driver

The first step is to download the SAS OLEDB driver.  This is freely available from the SAS web site, but it's not completely easy to get to.  Here's the link to the information about the SAS driver:  http://support.sas.com/documentation/tools/oledb/gs_olap_install.htm.  When you go to that page, you'll see something that looks like this:

You'll see that they give you the link to the download page, but they don't hyperlink it; curious, no?  Here's a clickable link to the OLE DB Providers download page:  http://support.sas.com/demosdownloads/setupcat.jsp?cat=SAS+Providers+for+OLE+DB

Unfortunately, SAS requires a login to access their downloads.  Fortunately, there's no commitment involved in creating a login to their site. Download the latest version of the OLE DB providers.

NOTE:  Business Intelligence Development Studio (BIDS) is a 32-bit application that does some funky stuff with providers.  If you're running on a 64-bit system (and especially if you're going to be scheduling a regular job), you may well want to download both the 32-bit and 64-bit drivers.  BIDS uses 32-bit providers during runtime and debug (design time), but can also utilize the 64-bit providers.  If you're just doing a one-off data load, the 32-bit drivers may well be sufficient for your needs.

Install the providers.  Note the selection below:  we only need to install the OLE DB providers.

I also typically deselect all but the minimum languages:

And after some checks, we're ready to install.  

Note that we're installing both the 32-bit and 64-bit providers here.  This was included in the SASOLEDBWeb_xxxxx_win_xx_web_1 download.

Design and Run your SSIS Package

Now that the SAS OLEDB drivers are installed, it's time to use them in SSIS.

The first step, once you've open BIDS and created a new project and package, is to create your connection manager.  Right-click in the Connection Managers window and select New OLE DB Connection...

One of the new options, now that you've installed the SAS providers, is "SAS Local Data Provider".  This is what you want to select:

You'll then see a window like the one below:

And here's the non-intuitive part:  the "server or file name" setting is the directory containing the .sas7bdat file.  You don't specify the filename anywhere in the connection manager.

Using the SAS OLEDB Connection

Now that you've got the SAS connection created, create a new Data Flow task and within that create a new OLE DB Source.  Select the connection manager you created in step one.  In the table or view field, you'll see a list of all of the .SAS7BDAT files in the directory you specified in the connection manager (highlighted below); this is the table source for the rest of your job.

Now that you've got your source set up, you can create data transforms, conditional logic, and the like, just as you would with any SSIS package.  Of course, most of us just want a straight data load, so we'd connect our SAS source directly to the DB destination, something like the picture below:

You'll notice there's a warning in the source object.  That's the result of the SAS file not communicating code page information to BIDS; generally speaking, this shouldn't be a problem.

Below is the actual warning window:

You can clear up the warning icon simply by telling BIDS to use the default code page in the source properties window, as below:

If that causes character encoding errors, and BIDS is unable to determine the code page, you'll need to specify the DefaultCodePage setting and change the AlwaysUseDefaultCodePage to true.

And that should be it:  you can import SAS data to SQL Server (or any other SSIS-supported destination, for that matter) without an additional SAS license or client install.