Moving SAS dataset into SQL Server WITHOUT using SSIS packages

I have a vb.net 2010 project that would remotely run a prepared SSIS package that exports a SAS 9.3 dataset into a SQL Server 2012 database. As far as I can tell, the process tested fine.

However, I was told not to use it (because SSIS packages are unreliable?), so are there alternatives to doing this?

I have looked up SAS ODBC to see if I can do this from the SAS end, but I am not sure of the DSN argument, the example I looked up is like this:

LIBNAME SQL ODBC DSN='sqlsrv_nt' schema=MSSQLTips;

Besides not being sure that the DSN is applicable for me, I am not administrator on my workstation so I can't play with ODBC settings much - I'm not certain that's the way to go.

Solution

You can either use a driver or DSN (customized shortcut data source connection object with all configurations/settings set). Once connected, append your data from local to remote source.

* WITH DSN;
libname mssqldata odbc datasrc="DSN Name" user="username" password="password";

* WITH DRIVER;
libname mssqldata odbc complete="driver={SQL Server}; Server=servername; user=username; pwd=password; database=databasename;";

** APPEND TO DATABASE TABLE;
proc datasets;
    append base = mssqldata.DBTable
    data = Work.SASdataset
    force;
quit;

** UN-ASSIGN ODBC LIBRARY;
libname mssqldata clear;

Be very careful with assigning library to database as it is a live connection and not copies. Hence, modifying/adding/deleting will reflect immediately to server.