Converting a SAS application into a working SSIS project

My journey began with unveiling my new mission that was given to me by the SQL Secret Services: To unravel if it was possible to convert a current SAS application that was run manually by a user once a week. I was to help the poor unfortunate soul get rid of this burden.

So I began my destined journey to complete the difficult but possible mission.

What was I to do???

The design and process was initially created on the SAS platform. The reason for the transformation was due to its somewhat old-fashioned structure, the extraction on SAS took hours to run and it used to put a huge strain on the source database.

This step involves obtaining the account numbers given from the client, the account numbers needed to match the account numbers from the database which reads from the source database, there was no recollection of getting those accounts numbers again, we would have to get the file and redo the process if the client needed the data again, no history was obtained for future use. The datasets created were temporary, once the SAS program is closed you would have to rerun the process.

The code on the SAS platform was easy to understand and decipher, converted for use in SSIS using simple T-SQL code.

The SAS code that was “encrypted”:

This was indeed a great mission!

I had familiarised myself to converting this code into a working Microsoft SSIS package. The objective of this mission was matching the account numbers with the account numbers given from the client, the data will then be put into SQL Database then exported into an Excel file, (simple hmmm)!!!

SAS GUI – Running the code to read the flat file then matching it to what is in the DB

What is Proc sql in SAS?

PROC SQL can be used to retrieve, update, and report on information from SAS data sets or other database products.

Some examples include:

• ·         Writing a SQL code using various styles of the SELECT statement.
• ·         Dynamically creating new variables on the SELECT statement.

o    Ex:

proc sql;

create table admin_order as

select a.*,b.Column1, b.Column2, b.Column3

from table a left join table b on a.Column1 = b Column1;

quit;

• ·         Use CASE/WHEN clauses for conditionally processing the data.
• ·         Joining data from two or more data sets (like a MERGE!)

So technically we can use SQL to read data from a database in SAS :)

What I have learnt once the temp tables are created, the user then would need to open the dataset created (which is like a table) in excel, in order to save the file in excel to send back to the user.

The idea:

Connection Mangers:

a.     OLE DB Connection – Connecting to the Database

b.     Excel Connection Manager – The output of the file

c.      File Connection Manager – The file that is given from the client

d.     SMTP Connection Manager – Completed file is sent back to the client

The SSIS Package:

My findings:

The conclusion:

In my time converting a SAS application to SSIS, I have to say the new package now takes under 60 sec as opposed to 10 – 20 minutes.

I have to say though, there are many BI tools out there, and there will always be the question which is better… That question for now will stay a mystery…. But for the mission given to me I would say SSIS won this round!

For more information:

http://www.sas.com/en_za/home.html

http://www.microsoft.com/en-us/server-cloud/solutions/business-intelligence/

http://www.microsoft.com/en-us/powerbi/default.aspx

The events depicted in this blog are fictitious. Any similarity to any database, package or table names is merely coincidental.

Events highlighted in black is used to protect client information.