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.

Using Error (and other multiple) Paths in SSIS

SSIS provides for multiple paths between tasks.  Very helpful stuff; here we’ll look at a simple solution to a common scenario.
Below is a job that stops a blocking service, reads from the source DB (SQL CE) file, truncates the destination table, and then copies the data from the source to destination.  Finally, it starts the service again.

What if, however, our test job fails after stopping the service?  We can provide for failure notification through SQL Agent jobs, but wouldn’t it be nice, also, to have the service start again, even after a failure?
We’ll set that up using failure paths, such that the tasks will go directly to the start service task in the event of a failure.

Creating a SSIS SQL Compact Data Source

There’s no out-of-the-box SQL Compact data source in SSIS, which presents a problem when you’re needing to copy data from a SQLCE data file.

 It turns out, though, that it’s easy to repurpose a OLEDB connection to read from a SQL Compact DB.

Simple Method to Validate Data Read at the beginning of an SSIS Package

We’ve looked at using transactions in an SSIS package to ensure that, for instance, the read data step in your package doesn’t fail after you’ve deleted the data it’s set to replace.
This is a very effective and really useful method, and it’s exceedingly flexible.
If your project spans multiple servers, though, this will require changes to the DST service settings that you might not be able to make.
There’s a simpler way, though it doesn’t offer all of the protections of wrapping your package in a transaction. We’ll take a look at that, here.

Utilizing Transactions in SSIS to Rollback After a Failed Import

SSIS is used primarily to import data into a database, particularly from flat files, but also from other databases. The typical SSIS package for doing this task looks something like the picture at the left: backup the current data, delete the current data from the destination table, and then import the data from the source.
This is all good, and it works well. When it works. The problem is that if there’s a problem with reading the data from the source (say, the credentials for the source database changed), you’ve already blown away the current (production) data. The job fails, and the data remains missing.

An Introduction to SSIS - A Beginning Step-by-Step Tutorial

SQL Server Integration Services (SSIS) is a *really* powerful data transformation and import tool; it allows for all kinds of data manipulation, both between databases and within them.
The problem is that it’s not entirely intuitive; the learning curve is steep. But you really shouldn’t let that stop you from trying it out: once you’ve got the basics, it’s really quite accessible.
In this series of posts, we’ll do some basic, step-by-step data manipulation with SSIS, starting with importing data from a CSV file into a SQL Server 2008 database. We’ll move on to copying data between Oracle and SQL Server.
For more complicated data, bulk insert allows for some reasonably full choices. You can read about using it in my post here.

Importing data from a CSV file into SQL Server

SQL Server allows for a few ways to import data from a text file, most directly with the import wizard, which is a pretty accessible way to import uncomplicated data. This, by the way, uses SSIS in doing the import, though it wraps it all in a wizard.
SSIS, though, offers the most flexibility, both in terms of data source and destination, as well as what you’d like to do with the data as you’re copying it.

How do I get SSIS?

SSIS is included with SQL Server (non-express editions). It’s installed by default, and you can specify its installation specifically by selecting the “Integration Services” components of SQL Server when you’re doing the installation.
Note, by the way, that SSIS does not require a SQL Server instance in order to run.

ORA-29275 when Accessing a UTF8 Database with SSIS

So when I first started looking into SQL Server Integration Services (SSIS), I was told that the learning curve was steep, and that it was worth it to learn what you're doing with it. Truly said. SSIS presents a myriad of possibilities for data, and once you get your head around some of the terminology, creating simple transformations and data imports is a snap. But what about when the problems you encounter span two different products? What if one is Oracle? What if it's 64-bit, on Linux? UTF8? That's really what SSIS is for, and it's possible. But. Lots of Googling. Here's one roadblock I encountered in importing data from an Oracle database into SQL server, along with an unsatisfying workaround. But it does work, and it's easy. If you know of a better solution, I'd love to hear from you!

The Problem

So here's the problem in a nutshell: the Oracle database uses a utf8 character set. SSIS, when connecting with either the OLEDB or ADO.NET data sources, would use something else. What, precisely, I'm unable to discern. I can say that the Unicode setting was set to true in the data manager. This was manifest by a startling Oracle error during the load from the data source: ORA-29275, which is a "partial multibyte character" error. This means that the data doesn't fit the database's character set, which (one assumes) is terrible: such data is almost by definition corrupted, and getting it back reliably is a tricky proposition. Oracle says, basically, that you've got bogus data when you see this error. I was prepared to believe that, as this error occurred even in a simple select statement from the DB server itself.

The Clue

The curious thing about this situation is that, in trying to figure out what was going on, another user had logged into the server using a different OS username. When he connected to the database using the same Oracle user ID, he didn't get any errors. Aha. Environment. NLS_LANG, to be exact. Setting that to American_America.UTF8 took care of the error on the server, and on clients running SQLPLUS, to boot. All should be well, correct? No. The SSIS package continues to fail. Oh, yes: the registry. Don't forget that Oracle stores client NLS data there, as well: HKLM/Software/Oracle/$ORACLE_HOME/NLS_LANG Watch out for any dangling NLS_LANG settings in HKLM/Software/Oracle That surely will fix it, right? Sadly, it didn't, and a desperation reboot didn't help.

The Work-Around

So Google leads me to hints that ADO.NET and OLEDB from Oracle don't really pay much attention to the local NLS_LANG settings. That appears to be the case, or, at least, they don't get their settings from the same place everything else does. So, I return, sadly, to ODBC. And it works! It works well. But it's so non-portable, and, let's face it: ODBC is not anything new and shiny; it'd sure be nice to have all of our SSIS packages all new, self-contained, and .NET-ed. So if you, like me, run into this problem, know that ODBC can be your friend. If you, unlike me, know of a better solution, please let us know! I'll post updates as I encounter them.

ORA-00911 when writing a query for SSIS

Coming from the Oracle world, I'm in the habit of putting a semicolon at the end of all of my SQL queries. Not a good idea when you're writing an Oracle query for import/export in SSIS (SQL Server Integration Services).

The problem is that SSIS takes care of that for you, and you'll get an entirely unhelpful "ORA-00911: invalid character" error message.
Just remove that final semicolon, and you should be in OK shape.

If you find yourself needing to do multiple SQL statements in one execute SQL task (for instance), you can use the GO separator instead of semicolons.

Note that GO has to be on its own line:

select 'this is sql statement 1' from dual
go
select 'this is sql statement 2' from dual
go

For the curious, GO is a SQL Server-specific batch keyword that is used to separate different SQL batches. It wouldn't normally be something you'd consider using in an Oracle query, but it works in this case.