SAS vs SSIS to load in flat file

Reply
Super Contributor
Posts: 418

SAS vs SSIS to load in flat file

Hello everyone. I have an interesting question regarding efficiency in getting data from a SAS dataset into a sql server database.

Up until now, I have been using the bulk load libname options in an odbc string. Example

Libname myserver odbc dsn='odbc_mydsnname' bulkload=yes dbmax_text=32000;

proc sql;

insert into myserver.Finaltable(variable1,variable2)

select varaible1,variable2

from WORK.sasdataset.;

quit;

run;

This was taking a very long time for certain jobs. Someone brought up that SSIS packages should be much faster than this process, so I created a flat text file and then loaded the flat file into sql server with a SSIS package. To my amazement, it took < 15% of the total time for SAS to do the insert into clause.

I am using Base SAS with the ability to do odbc connections, but no SAS etl package. Is it a known fact that SSIS is a faster tool than base SAS for data loading, or is this massive performance difference (minutes instead of hours) due to the fact that the SSIS package runs on the sql server, which the base sas runs on a local machine?

If anyone has any information on this, or a more efficient way to load very large amounts of data quickly I would love to hear it.

Thanks very much for your time!

Brandon

Occasional Contributor
Posts: 18

Re: SAS vs SSIS to load in flat file

Posted in reply to Anotherdream

Hi Brandon,

This might solve your problem. Please check and confirm.

buffers = 8;

proc sql;

insert into myserver.Finaltable(variable1,variable2)

select varaible1,variable2

from WORK.sasdataset.;

quit;

run;

Super User
Posts: 5,427

Re: SAS vs SSIS to load in flat file

Posted in reply to Anotherdream

Are you sure that bulkload is taking place, I think there are more requirements to make this actually work other than specifying the bulkload option.

And about the comparison, of course will the file transfer make a difference in the overall load time, how much depends on the network. So to make a fair comparison, you need to add the time to export the data to an external file, copy it to the RDBMS server, and the load time for the SSIS package.

Data never sleeps
Super Contributor
Posts: 418

Re: SAS vs SSIS to load in flat file

Posted in reply to Anotherdream

Hello LinusH. I had the administrator run a trace on the sql server, and when I am inserting into the table (with the bulkload option) the sql server is doing a Bulk Insert. However it does not show where it is getting the base data from.

And for your second question (this is why  I am puzzled by my findings). It takes sas 20 seconds to output the flat file to a network drive, from there it takes SSIS 5 seconds to read in the data. Note I am not writing the data directly to the Network drive of the RDBMS server (I don't have access), but am writing it to a network drive that exists in the same physical location as the server (Shelton, United States).

So the combination of outputing the flat file and reading in the data in the SSIS package takes 25 seconds, while doing the insert into took over 228 seconds from SAS, even when the SAS system is running on a VM that is located in Shelton.

Thanks, and let me know if something I am seeing does not make sense!

Ask a Question
Discussion stats
  • 3 replies
  • 1437 views
  • 0 likes
  • 3 in conversation