BookmarkSubscribeRSS Feed
beobregon
Calcite | Level 5

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

7 REPLIES 7
Reeza
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5

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
Opal | Level 21

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 7 replies
  • 2339 views
  • 0 likes
  • 4 in conversation