Friday, June 22, 2012

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:

[SAS OLE DB Source [1]]: Cannot retrieve the cloumn code page info from the OLE DB provider.  If the component supports the "DefaultCodePage" property, the code page from that property will be used.  Change the value of the property if the current string code page values are incorrect.  If the component does not support the property, the code page from the component's locale ID will be used.

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.

18 comments:

  1. Hi Lane, Thank you so much for this post ! Expecially the non-intuitive part which helps a lot ! Did you ever have to import a SAS date column from a SAS7BDAT file into a datetime column from a SQLServer Table ?

    ReplyDelete
    Replies
    1. Hi, I've not done that directly, but I think we could fix any problems you're running in to; are you getting an error during the import?

      Delete
  2. CozyRoc has a component that allows for the reading and writing of sas7bdats inside of SSIS. It is a binary read/write and handles items like date transformations.

    On the date/datetime issue, SAS uses an epochal date of Jan 1, 1960 and a datetime starting at midnight on that date. SAS stores all values as doubles. To handle the date/datetime, simply add in the number of seconds since the SQL Server epochal and that will do the conversion.

    ReplyDelete
  3. Thank you so much Lane! Really appreciate this step-by-step guide with screen shots. I'm an entry-level analyst and not terribly familiar with different programing environments so this made my day!

    ReplyDelete
  4. Thank you Lane. Very helpful tutorial!

    ReplyDelete
  5. Hi, I have done it on my PC it works! however i cant install the software on Windows Server any help?

    ReplyDelete
  6. Hi...Thank you lane for the tutorial. For SAS source have you ever chosen AccessMode as SQLCommand?

    ReplyDelete
  7. Hi- is there any way to write a SELECT statement instead of just using the Table (i.e. file) reference? I would like to limit the amount of rows read, so need something like this: SELECT TOP 10 * FROM EFX_FINAL_FILE

    ReplyDelete
    Replies
    1. Ah, now that's a great question. The SAS OLEDB provider doesn't allow for this, but depending on what you're trying to accomplish, you can get at a similar end result:
      if you're just wanting to get only the first x rows into the DB (or whatever), you could read the contents of the .sas7bdat file into a variable or a temporary table, and then insert the first x rows of either of those into the permanent location within the database.

      If you've got a huge file and are wanting to avoid reading the entirety of it, because it's prohibitively time-consuming (or something along those lines), this probably won't work well for you. If that's the case, let me know, and we'll see what else we can come up with.

      Delete
    2. There's a utility called the SAS System Viewer that might allow you to export all of your data into a text file, which then isn't too hard to truncate using powershell or similar.

      Delete
  8. Very helpful
    Is there a way to load mainframe SAS .dat directly into tables? Not sas7bdat

    ReplyDelete
    Replies
    1. I've not had any experience using native SAS .dat files, though it sounds like, if you've got access to SAS client tools, you can do this:
      http://stackoverflow.com/questions/1429187/can-you-use-a-sas-dataset-as-a-ssis-data-source

      Delete
  9. Hi Lane,
    I am a SAS and SQLserver user in Japan.
    This is very nice. Time to load shrunk drastically.

    TI

    ReplyDelete
  10. Hello Lane,
    How to read the contents of the .sas7bdat file into a variable or a temporary table? Can this be done in SSIS?

    ReplyDelete
  11. Hello Lane,

    Very nice article! I was wondering whether we can use table name or view name variables to get the get the name of the table or the view using the .sas7bdat file.

    Thanks!

    ReplyDelete
  12. Thank you for the detailed instruction. It was very helpful

    ReplyDelete
  13. VERY HELPFUL ++++
    Big THANKS :o)

    Steve

    ReplyDelete

Thanks for leaving a comment!