Home > Uncategorized > Loading SAS files with SSIS

Loading SAS files with SSIS

Hi

I’ve been given an assignment to load data from a SAS Institute data file to SQL Server using SSIS. How hard can that be I thought to my self. All I needed to do was to find the oledb provider for SAS, install it on my machine, and use it within SSIS, a no brainer!

Well first it took some time for me to actually find the oledb provider. You see SAS Institute provide it for free, which is great, but they kinda keep it in a secret place. If you google “sas oledb provider” the first result is this, which is a general documation spot for the SAS Oledb Provider, which is a great source for content regarding how to use the provider, but where to download it? Well you need to click on some links, only to get to a page that tells you where you can download it, but guess what, the part that tells you where to download it from is NOT a link, it’s URL indeed, but it’s not marked as a link, and very hard to find in all the other text on the page. Unfortunately I’m not a reader, I’m a scanner (we’ll get back to that later also). When I need to read something fast, which is almost always, I scan the text looking for known patterns instead of actually reading the entire content. So when I look for a link for downloading the file, I tend to not read any irrelevant text, which in this case was the link, disguising as normal text. But I’m a good person, so I’ll help you guys with that, here is the direct link to the download page, if you need to download it. You’ll need to register and confirm by an email first.

Now that I got the provider, I opened SSIS and did the following:

  1. Created anew connection manager to my SAS file using the “Native OLE DB\SAS Local Data Provider 9.3″
  2. In the Server or FileName I wrote the full path of the file name (which we’ll later go back to)
  3. Pressing Test Connection to make sure it works and pressed OK
  4. Added a new OLE DB Source to my data flow task
  5. In the OLE DB Source Editor I tried to pick the table in the SAS file, only to get an error message with the following content: “fn =ydedopn; context =sasej9lib.dll.”

So I thought I’ll google that, and guess what, one result only, to a linked in post of a guy asking for help with the same problem, no one with a solution.

I thought well let’s try and isolate the issue, so I started windows console project, to test to connectivity logic using an OleDb provider to the SAS file. I’ve spent 2 hours on this, trying with all kind of different ways to make it work, but either I got an empty data table, or the same error message as in SSIS. Frustrated I kept on going back and forward between the SAS website documentation, looking for a hint, and there, just before I was to give up, I saw the problem!

In the documentation it states that the datasource property is “A physical directory that contains the SAS data set that you want to access with the connection.” How did I missed that? (damn you scanners!) I shouldn’t have used the file name, but the file directory! The provider treats the folder as the “database” and each file is a “table” in the “database”!

Changing that simple fact, I was able to select a table from the Ole Db Source Editor, and beside some page code warnings which I’ll look into later, everything works as is should.

So the conclusions are:

  • don’t scan the documentation, read it! (this conclusion is pointed to me only)
  • when pointing to a SAS Institute files use only the directory in the data source!

I hope this will help others

/Rafi

Categories: Uncategorized Tags: ,
  1. February 9, 2012 at 1:28 pm

    Rafi,

    I’m not sure if you stumbled upon this in your searching, but I discuss the SAS OLE DB provider with some example scripts here:

    http://blogs.sas.com/content/sasdummy/2010/11/03/peek-at-your-data-using-vbscript-ole-db-and-the-sas-local-data-provider/

  2. Jonathan
    February 21, 2012 at 10:36 am

    Hi Rafi, thank you…. great post.

    Now I’m able to create an oledb connection task, in wich i can see all the columns of the sas data set, no problem even when I add my OLE DB destination (a sql server 2008 table) and every thing is fine with the column mapping. But when I run the package unfortunately I get a generic error. and the data flow task fails.

    Here’s ssis errors:

    [OLE DB Source [436]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E04.
    An OLE DB record is available. Source: “” Hresult: 0x80040E04 Description: “An unknown error occurred.”.

    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “OLE DB Source” (436) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

    some technical details:

    – I’m using SAS Local Data Provider 9.1

    Do you have any idea of what can be my error?

    thank you so much in advantage

    • February 21, 2012 at 10:45 am

      Hi

      The error msg is rather useless, so it’s hard to tell without knowing the bigger setup.

      Try to simplify the case, and see if you can solve the issue in a smaller scale first.

      You can also try and write some code, outside of SSIS, to see if you can get an oledb connection to work with the file.

      Sorry I can’t help you with your issue, but please notify me when you find a solution for your problem.

      \Rafi

      • Jonathan
        February 21, 2012 at 3:26 pm

        Hi Rafi,
        I’ve done the upgrade of the SAS Local Data Provider up to 9.3, and now the problem has solved.
        the SSIS now works perfectly.
        there’s still only a problem… inside the sas dataset I’ve a DATE9. field. When ssis pushes those values inside a date column in sql server table, the output is wrong.

        i.e:
        -SAS value 28FEB2012
        -the ssis oledb component read it as DT_R8 (19501)
        – I attach in the pipeline a conversion task to DT_DATE, but finally in my sql tabel i get 27FEB1952 value)
        It seems like sql date start from 01/01/1900 whereas SAS starts from 01/1/1960.
        It’s there a common approach to upload sas date formats into sql server table ..or do I have to convert it inside sas program?

        thanks for your help Rafi.
        bye.

  3. Elena
    March 2, 2012 at 8:51 pm

    Hi Rafi,

    You made everything is right until number 4 in your order list. After you drag OLE DB Source open Advanced Editor. In Connection Manager tab choose your newly created SAS connection. Then in Componnent Properties tab in ‘OpenRowset’ point into your table name. Check if you get connections between columns in Column Mappins tab. Click OK.

  4. CTB
    April 14, 2012 at 2:21 am

    Rafi,

    This blog post was in a response to a question I posted in the MSDN forums (read http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/f18cdc4b-23cb-4782-97ac-3d0e8df90483 for more info)

    I am not familiar with SAS at all (I don’t know what the SAS file extensions are). Are the *.WPD (WPS DATA SET FILE) the standard SAS file extensions?

    I need to import this WPD file into SQL Server (or parse into a plain text flat file (CSV file prefered)).

    Do you have any advice for me?

    Thanks,

    CTB

    • April 14, 2012 at 6:28 am

      Hi

      I know very little to SAS. I was told to get them into SQL Server, so I did. The files I was loading had a sas7bdat extention.

      I do not know if wpd is a SAS file, and if so, which SAS file it may be. Try to contact some SAS dudes at a SAS support forum.

      Good luck!
      Rafi

  5. Vince
    May 10, 2012 at 5:26 pm

    Following your instructions got ride of the error message but there are still no tables recognized. I put all the SAS files in the same folder and specify the folder in the connection. No tables come up when selecting the name of the table or the view in the OLEDB source editor.

    • July 16, 2012 at 1:02 pm

      Hi Vince

      A bit late to reply, but I think that this may be caused by having an earlier version of the local SAS provider.

      Regards
      Rafi Asraf

  6. Ranaip Dey
    September 6, 2012 at 12:13 pm

    Thank you very much, it is really a useful post, it helped me a lot.

  7. Carl
    February 13, 2013 at 9:21 pm

    Thank you! You saved me sooo much time!

  8. Betty
    March 7, 2013 at 10:10 am

    Thank you for this article who was very helpful.
    I still have a little problem when communicating between SSIS and SAS. I am doing some tests and I have a data flow task who reads data (4 lines) from a text file to insert into an OLE DB Destination (SAS).
    When I start this task, the flat file source reads the whole 4 lines but insert only one (the first one) into the SAS table and oddly the mapping freezes in yellow and tell me :
    “Package execution completed. Click here to switch to design mode, or select Stop …” whereas it is not finished for me…

    In the Progress section, no error was found.

    What is the most incredible is that sometimes without any changes the mapping run entire and gets green with inserting all the lines.

    I noticed that in the SAS log there is a lot of connection/deconnection to the server. Is it possible that SAS closes the connection before inserting all the lines ?
    Any other idea ?

    Thanks in advance :)

  9. Dave
    July 24, 2013 at 4:35 pm

    Rafi – this was very helpful for connect to PC SAS. The following is my issue.
    I am trying to connect using SSIS to SAS that sits on UNIX.
    1. Can it be done?
    2. If so, which SAS Data Provider does it need be?
    3. What settings need to be set to connect.

    Thanks.

  10. Elena
    August 20, 2013 at 8:18 pm

    Rafi,

    Thank you for an interesting experience.
    I tried to follow the steps you share with us but still have a problem: after I created a new connection manager “Native OLE DB\SAS Local Data Provider 9.3” and pressed Test Connection to make sure that it works I’ve got an error:
    “Test connection failed because of an error in initializing provider. Not enough storage is available to complete this operation.”
    Does anybody know why it happens?

    Thanks

  11. Rohit
    October 16, 2013 at 3:46 am

    Hi Rafi

    Thats a great post, though I would like to connect to SAS data from my SQL Server management studio (like using T-SQL). Is there any way to create a Linked server from SQL Server to SAS? And if yes, what is the provider which will be used for this?

    It would be very helpful if you can help me out here..

    Regards
    Rohit Malik

  12. November 18, 2013 at 2:36 am

    I need to produce a report that contains historical data of some sort based it’s Tuesday snapshots in the year 2009 and 2010. Then I found this Intnx function to help. For example: To get a list of Tuesdays starting Jan 6, 2010 into a dataset called tuesdays: %let start_date=’06jan2009’d; data tuesdays ; do i = 0 to 103; Tuesday=put(Intnx( ‘Day’ , &start_date ., i*7 ),date9.); output; end; drop i; run ; proc print data=tuesdays; run;To load all Tuesday’s SAS data files: %let start_date=’06jan2009’d; %macro load_tuesday_sas_dataset; %global tuedate; %do i=0 %to 103; data _null_; call symput(“tuedate”,left(put(Intnx(‘Day’,&start_date., &i. * 7),date9.))); run; %put &tuedate; data dataset_&tuedate.; set filelib.sasfile_&tuedate.; run; %end; %mend load_tuesday_sas_dataset; %load_tuesday_sas_dataset; Enjoy learning SAS, a little bit at a time every day.

    • November 18, 2013 at 6:49 am

      Dear August,

      I’m afraid I cannot help you with this. I know very little about SAS.

      I hope you’ll find a solution that works for you soon,

      Yours
      Rafi Asraf

  13. Nidheesh Pillai
    January 8, 2014 at 11:21 am

    Hi Rafi – I request a help from you,

    I have a requirement to convert/migrate code which was previously written in SAS into MS-SQL or SSIS format and completely do away with SAS and its high-cost licence. I have the original SAS code files with me. Currently our plan is to transform the entire SAS code logic into SQL logic, which is a little tedious because (i) people are unaware of SAS in my team and (ii) we might lose important information if we were to make a mistake during code conversion. It just hit me that it might be possible to just add a provider (if available) for SAS into my SSIS package and execute the SAS code using that provider, which was when I stumbled upon your page.

    I had few questions –

    Q1. I wanted to know that do we require licenced SAS software installed on my SQL environment prior to proceeding with installation of the OLEDB provider for SSIS?
    Q2. What will be the SAS OLEDB provider verson for SQL Server 2008?

    Please help. If others online too can suggest something based on the questions or on the approach, it will be of great value.

    Many thanks!

    • February 6, 2014 at 8:46 am

      Hi

      I’m afraid I’m not able to help you, I’m not a SAS specialist, neither do I have insight in SAS drivers.

      If you find a solution, please post it here, so that others can enjoy you achievement.

  14. ashley
    February 3, 2014 at 11:29 pm

    Hi Rafi,

    Thank you for the post. I’m using SAS Local Data Provider 9.3, trying to read SAS .xpt files. In the folder, I’ve multiple .xpt files. Tested connection without error, but couldn’t load any tables. Is this the version issue?

    • February 6, 2014 at 8:46 am

      Hi

      I’m afraid I’m not able to help you, I’m not a SAS specialist, neither do I have insight in SAS drivers.

      If you find a solution, please post it here, so that others can enjoy you achievement.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: