12-11-2013 10:47 AM
I have a request to automate a process which must run a SAS program and then use SSIS to import the results from the SAS process into a MS SQL Server based data warehouse. I know very little about SAS so I am looking for anyone who has technical knowledge of SAS and perhaps has called/launched SAS from a SSIS Execute Process Task? If so, can someone provide the steps in which you were successful in launching the SAS standalone executable engine or perhaps if you have a server version how you called the server version to run the SAS process?
12-12-2013 02:10 PM
The simplest way to execute a SAS batch job is to do it from an operating system command line. This link for Windows explains the details:
The SAS command would have to be executed on the server where SAS is installed.
An alternative approach might be for the SAS process to be scheduled independently, then have SAS load the results as tables into SQL Server. This is likely to be easier to set up rather than trying to "control" SAS from SQL Server.
01-16-2014 11:40 AM
Thank you for pointing out some different options for this. At this point the decision has been made not to invest in a SAS server and they are looking at other alternative ways of getting the data into the SQL Server database. I will however let them know that your last option might be a viable alternative.
01-16-2014 12:39 PM
Did you have SAS and you didn't want invest in another SAS server license?
As another flavour of the alternative to have SAS populate SQL Server (requires a SAS /ACCESS license), you could have SAS create some csv files, which your SSIS process could import.