Bulk Insert from SAS (or openrowset)

Question

Bulk Insert from SAS (or openrowset)

Marcia Q

SSCrazy

Points: 2648
More actions
March 24, 2009 at 10:22 am

#129984

We need to pull some data in from our SAS db into a SQL Server 2005 database.
From what I can tell it's possible, but I haven't really determined exactly how to make it happen.
Has anyone done this and can offer some guidance?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

db2mo Ten Centuries Points: 1341 More actions · Answer 1

Did you figure out how to accomplish this? I've got SAS on my PC, not on a server and I want to load the output from the SAS file to SQL Server 2008. I know nothing about how SAS works. Any help would be much appreciated.

steveb. SSC-Forever Points: 46733 More actions · Answer 2

if you have the correct interface (ACCESS) license you can do this with a PROC EXPORT, otherwise use PROC EXPORT to create a CSV and then import this with SSIS

db2mo Ten Centuries Points: 1341 More actions · Answer 3

How do I know if I have the correct interface? All I was told was the the SAS is intalled locally on my machine. There is no server that we access to get data. The data is pulled off the mainframe.

steveb. SSC-Forever Points: 46733 More actions · Answer 4

If this is Base SAS then you should be able to see the license on the about window. Other flavours of SAS i am not sure, you could always run the PROC EXPORT and see if it errors.

db2mo Ten Centuries Points: 1341 More actions · Answer 5

I looked under the "About" and it says I have SAS 9.1 TS Level 1M3 XP_Pro Platform. So do I perform the proc export within SAS? As I said earlier I have never done anything with SAS.

steveb. SSC-Forever Points: 46733 More actions · Answer 6

db2mo (7/26/2011)
I looked under the "About" and it says I have SAS 9.1 TS Level 1M3 XP_Pro Platform. So do I perform the proc export within SAS? As I said earlier I have never done anything with SAS.

I would , try it first with the database connection and if this fails due to license issues then it will show up in the log. Then all you need to do is change th DBMS(?) flag to CSV and use the CSV to load into SQL Server.

db2mo Ten Centuries Points: 1341 More actions · Answer 7

There is an Export Wizard in SAS where I can export the file to a csv or txt file. How would I then take the csv file and pull it into ssis? I've never used SSIS.

steveb. SSC-Forever Points: 46733 More actions · Answer 8

db2mo (7/29/2011)
There is an Export Wizard in SAS where I can export the file to a csv or txt file. How would I then take the csv file and pull it into ssis? I've never used SSIS.

Best place to start is to use the import/export wizard in sql server management studio. Right click on the database and select import data to begin.

This will guide you through the process and at the end it will give you the option to run the package and save it as well. You can then open this in BIDS if needed

db2mo Ten Centuries Points: 1341 More actions · Answer 9

I was able to get the SAS ODBC driver downloaded and configured. I know it's working because I can import the data into Excel using that SAS data source. I was also able to create a shared data source in BIDS and the test connection was successful. The problem now is when I try to set up the dataset. I click on Query Designer then when I go to add a table, I get the following error message: "Attempted to read or write protected memory. This is often an indication that other memory is corrupt." any thoughts or suggestions?

I am waiting to get SSIS installed on my machine before I can go the route suggested in the last post. I will try that once I get it installed. In the meantime, I was going to see if anyone had any ideas on the current issue.