How to convert SAS export file (.xpt) to SAS data files in SSIS package?

[beobregon]

Hello there,

I have the following requirement. One of our data sources send us SAS data sets as part of a SAS export file (.xpt). We need to upload those data sets into SQL Server. So, we were planning to design an SSIS package that were able to extract the data sets from the XPT file to a folder and then using SAS ODBC driver upload them into SQL Server.

M problem is that I have not been able to find an application for windows that will do the extraction work in SSIS for us (I meant, something similar to what the 7z.exe does for a zip file.)

Is this even possible to extract the data sets from the xpt file using SSIS?

Thanks a lot in advance,

BOGE

[Reeza]

Not directly within SSIS.

You need SAS or a third party tool to read this file. Or a partner who can convert it to a different file format for you, though the third party tools appear to be cheap. I don't believe there's a python or R package to read XPT files, though they can read sas7bdat file.

It's been answered here on SO

http://stackoverflow.com/questions/1429187/can-you-use-a-sas-dataset-as-a-ssis-data-source

Or ask your client to send you CSV files. 

[beobregon]

Hey Reeza, Thanks so much for replying!

Reeza, we are actually already loading the data set to SQL Server, but it involves a lot of manual interaction. We are looking for automate the process that's the reason for trying to use SSIS.

I will take a look to the post that mentions using "STATTransfer" tool. I will let you know my findings.

Meanwhile, if anybody knows a way to automate this process please share 🙂

[ballardw]

If the objective is load a file into another database then options abound. Cheapest would be instead of creating an XPT file to export from SAS to something like CSV that the other application can read.

Another option would be to license SAS/Connect for SQL server which should allow you to write directly to, and read from, the database from SAS.

[beobregon]

Hi Ballardw, accesing SAS directly is not an option, the sender is completely another institution sending us information. We use sFTP to transfer the files.  Regarding (.csv) hm, ... well that may be our last option. As I already mentioned, we are already loading the data sets just wanted to find a way to automate the process using the xpt export file if at all possible.

Thanks!!!

[Patrick]

we are already loading the data sets

So you have a way to read .xpt "manually" on your side? How are you doing this right now? May be that can get automated.

[beobregon]

Hi Patrick,

Yes, I am suspecting that I will need to talk to our SAS developers to find out how they are currently converting the xport file to data sets and saving the files in a share path. That may be the way to go.

I will update the post asap.

BOGE

[Patrick]

You can execute a SAS program in batch via operating system command line. You could issue such a call out of SSIS I believe.

If your site also got the SAS access modul for SQL Server licensed then you could have this SAS program load the data directly into SQL Server staging tables.