10-06-2016 05:57 PM
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,
10-06-2016 06:22 PM
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
Or ask your client to send you CSV files.
10-07-2016 10:50 AM
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
10-07-2016 11:17 AM
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.
10-07-2016 11:29 AM - edited 10-07-2016 11:30 AM
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.
10-09-2016 02:25 AM
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.
10-11-2016 11:26 AM - edited 10-11-2016 11:27 AM
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.
10-11-2016 07:20 PM - edited 10-11-2016 07:21 PM
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.