Using SSIS to export into SAS JMP File Format
Has anyone out there worked on a project to export data from a SQL Server Database into the SAS JMP file format? I want to create an SSIS package to take snapshots of our database at regular intervals and export the data directly into a SAS JMP File. I have no idea how to go about doing this. Thanks in advance for any help. Letni.
July 9th, 2007 9:04pm

Is JMP a proprietary format? Or is it just a text file?
Free Windows Admin Tool Kit Click here and download it now
July 9th, 2007 9:06pm

I believe it is proprietary.. but wondered if it was really just a .csv named with a .jmp extension.
July 12th, 2007 10:31pm

mr.letni wrote: I believe it is proprietary.. but wondered if it was really just a .csv named with a .jmp extension.You'll have to ask the SAS guys.
Free Windows Admin Tool Kit Click here and download it now
July 12th, 2007 10:36pm

Hi,I have written an SSIS 2005 data adapter for reading the SAS binary file format *.sas7bdat, but not one to write data out into it. Something similar may be possible for the JMP format.I used the ADO example as a base and then the SAS OLE-DB Provider (which comes when you have SAS or JMP) had .NET code example.After a lot of googling I found code that showed me how to use the OLE-DB driver in C#. The author of that code suggested I would have issues where the data within the SAS file had formats, but in fact i had no problem for my datasets.Unfortunately I can't release it, but it's begging to be implemented as a product. I would pay up to $2,500 for it.Alex
December 19th, 2008 6:20am

Letni,I was wondering why you are not using JMP software itself to import data into JMP format? You can use JSL to automate the process.Do you have documentation available for JMP format? Is it publicly available? SSIS Tasks Components Scripts | http://www.cozyroc.com/
Free Windows Admin Tool Kit Click here and download it now
December 20th, 2008 7:10pm

mr.letni said: Has anyone out there worked on a project to export data from a SQL Server Database into the SAS JMP file format? I want to create an SSIS package to take snapshots of our database at regular intervals and export the data directly into a SAS JMP File. I have no idea how to go about doing this. Thanks in advance for any help. Letni. Hi Mr Letni:I will answer your question, and provide more contextualinformation about SAS (for those who have not used it). I have been a SAS user since 1991, and have presented at many SAStechnical conferences. I recently have earned MCT credentials in the Microsoft world. These forums are frequently read (and easily searchable),so I believe spending time on a more comprehensive answer will help people with similar SAS and SSIS issues in the future. (All links are current as of December 20, 2008.)JMP (for those who don't know) is the SAS spreadsheet-like standalone software. I don't know many people using this software, since most users I know prefer either display manager (the classic Base SAS) or have migrated to Enterprise Guide (usually version 4 or higher).Enterprise Guide provides a GUI interface similar to the Microsoft SQL Server BI stack, and has similar functionality to SSIS. SASprovided business intelligence and predictive analytics across many platforms, and with the release of version6 (back in the 1980s),SAS was a leader in moving data across platforms (such as Windows, Unix, and Mainframe). The SAS version 6 framework was the main standardization effort on which SAS has built many derivative products, including industry-specific solutions. I view that standardization similar to what Microsoft did with the introduction of .NET, though of course SAS must run on an operating system and many (maybe most)SAS installations run on Windows. These frameworks means that there are now multiple ways to achieve results, and I have (as a heuristic) said that many times there are at least five ways to do accomplish what you are wanting to do in SAS, the differences being in what products you have licensed and in what solutions offer better performance.Your question has multiple answers, and I am providingtwo recommended options.First, I looked atthe JMP version 9 documentation (seehttp://www.jmp.com/support/downloads/pdf/jmp8/jmp_user_guide.pdfand, for all the documentation,http://www.jmp.com/support/downloads/documentation.shtml). The following informationis from chapter 2, page 13, and provides my first recommended solution, MDB:Importing DataIf you have data that exists in a format other than a .jmp file, you can import it and save it as a JMPdata table. The list below gives the file types you can import into JMP. Microsoft Excel (.xls), Microsoft Excel 2007 (.xlsm, .xlsx, .x$sb) on Windows with a V3+ compliantODBC driver Text (.txt) Text with comma separated values (.csv) Tabbed separated values (.tsv) SAS transport (.xpt, .stx) files Minitab files (.mtw, .mtp) FACS (.fcs) Microsoft Access Database (.mdb) on Windows with a V3+ compliant ODBC driver Database (dBASE) (.dbf, .ndx, .mdx) on Windows with a V3+ compliant ODBC driver MySQL, Oracle, and PostgreSQL on Linux with a V3 + Unicode compliant ODBC driver OpenOffice spreadsheets (.sxc) on Linux Data (.dat) files HTML (.htm, .html) on Windows SAS versions 6-9 (.sd2, .sd5, .sd7, .sas7bdat) on Windows SAS version 6 (.sas7bdat, .ssd, .ssd01, .saseb$data) on Macintosh and LinuxNote: Opening a SAS dataset directly is different from connecting to a SAS dataset through integrationwith a SAS Server, which allows JMP to access any datasets that the SAS Server can. See SAS Integrationchapter for details.I have writtenSAS/AF productionapplications, and I prefer using MDB (Microsoft Access) as the portable format for transferring data in Windows (either to or from SAS applications). You may have issues in transferring dates and times, andfor those issues insteadask your question not here but at thefree forums athttp://support.sas.com. Once your data are in MDB format, you can read them intoSAS. I do NOT recommend CSV orText files because they do NOT preserve the data types (meaning character or numeric);text formats like CSV should only be used if that is your only solution. Also, though ODBC is supported, most production applications have moved to OLE DB, and you should also if you are considering a driver.Thus, here is a second way which you may prefer using SSIS. You can use the SAS OLE DB driver. Someone on this thread (Alex Thomas) mentioned paying for one, and Idon't know why you have to pay for one since SAS provides one if you have a SAS license (most license packages should have this driver if you are using Windows). You can download thisdriver from thewebsite if you can provide a current license number. Otherwise, contact SAS for licensing this driver. Alex Thomas may have been asking for a SAS OLE DB driver to purchase, and there was a product called DBMSCopy, but they were bought out by Data Flux (a SAS subsidiary) -- I don't know if there is a replacement product today. Unlike many Microsoft products, early on, the SAS legal team created a sophisticatedlicensing infrastructure which has helped build it into a billion-dollar company. Today, SAS faces economic competition from the Microsoft SQL Server BI stack and open-source products like the R language. Consultants like me need to know what solutions are available from the SAS directionand the Microsoft .NET (and SQL Server) direction too, so that we can best provide our clients with the best combination of features and performance.Seehttp://www.sas.com/apps/demosdownloads/setupcat.jsp?cat=SAS+Providers+for+OLE+DBfor the OLE DB provider, andhttp://support.sas.com/kb/31/285.htmlfor information on how the ODBC and OLE DB drivers are bundled (if you have the original SAS installation disks).Note that I recommended two solutions, but that I listed more than five solutions for thisquestion. The documentation andSAS conference papers (use http://support.sas.com or http://www.lexjansen.com to search) often provide tips on how to achieve better production performance givendifferent situations. Mark Tabladillo MCT, Microsoft (SQL Server Business Intelligence, Data Mining) and SAS Consultant -- Atlanta, GA
December 21st, 2008 12:19am

Folks, There appears to be confusion. I am talking about custom SSIS data adapters to read and write proprietory SAS formats (eg *.sas7bdat, *.jmp). The custom adapter I wrote enables an SSIS package designer to drag-and-drop a configurable widget which natively _reads_ (but not writes)*.sas7bdat files. This component in turn employs the SAS OLE-DB provider component, which comes bundled with various SAS products such as BASE SAS and JMP. For decision-makers unfamiliar with the detail, unless SSIS can deal natively with proprietory formats then the overall solution is necessarily manual or more complex because somewhere in the chain you have to have a disconnected offline conversion process (typically someone with SAS on their PC who picks up text files from one folder andthen writes them in SASformat and drops them into another).At the end of the day you are typically trying to automate the work of a SAS process, yet find that you actually need a more complex solution that still needs SAS capabilities anyway (to do the data type conversions). It is possible of course to make a call to the OS from within SSIS to execute another process, but then we are talking complexity, security issues and you need a different and arguably more expensive type of IT person (an integrator vs a package designer). Hope this helps clarify the requirement. Alex
Free Windows Admin Tool Kit Click here and download it now
January 30th, 2009 3:10am

Also, contact: Alan Churchill www.savian.net Office: (719) 687-5954 Cell: (719) 310-4870
January 30th, 2009 3:19am

Alex Thomas said: Folks, There appears to be confusion. I am talking about custom SSIS data adapters to read and write proprietory SAS formats (eg *.sas7bdat, *.jmp). The custom adapter I wrote enables an SSIS package designer to drag-and-drop a configurable widget which natively _reads_ (but not writes)*.sas7bdat files. This component in turn employs the SAS OLE-DB provider component, which comes bundled with various SAS products such as BASE SAS and JMP. For decision-makers unfamiliar with the detail, unless SSIS can deal natively with proprietory formats then the overall solution is necessarily manual or more complex because somewhere in the chain you have to have a disconnected offline conversion process (typically someone with SAS on their PC who picks up text files from one folder andthen writes them in SASformat and drops them into another).At the end of the day you are typically trying to automate the work of a SAS process, yet find that you actually need a more complex solution that still needs SAS capabilities anyway (to do the data type conversions). It is possible of course to make a call to the OS from within SSIS to execute another process, but then we are talking complexity, security issues and you need a different and arguably more expensive type of IT person (an integrator vs a package designer). Hope this helps clarify the requirement. Alex Alex,If you already have SAS OLE-DB provider, why do you have to implement additional adapter? You can read OLE-DB sources with the "OLE DB Source" component.SSIS Tasks Components Scripts | http://www.cozyroc.com/
Free Windows Admin Tool Kit Click here and download it now
January 30th, 2009 5:44pm

MarkTab / CozyRoc I have come across this thread, looking for an answer that is not totally related to the original question. I will ask it here though because you two have mentioned in a round about way what I am trying to do. I am trying to use the OLE DB source object in SSIS to access SAS. I have the SAS OLE DB drivers installed, and I am using the SAS Shareport driver in particular. Currently I am getting the error that my userid does not have permissions to the lib that I want to read. I know this is an erroneous error msg though because using PC SAS I can get to the lib and view the data. Is there any walk through on how to set up the connection mangers and/or the OLE DB Object that I can reference to use this OLE DB driver? Have you ever used the OLE DB object to read SAS data? I have looked all over the web for this and I cannot find it anywhere. If you have any information or can point me to someone who can help me it would be a HUGE help. Jim
March 29th, 2010 5:44pm

CozyRoc have just released new adapters for reading/writing SAS's sas7bdat files. These are the relevant components: CozyRoc SAS Data Source - for reading sas7bdat files. CozyRoc SAS Data Destination - for reading sas7bdat files. The new adapters doesn't require installation or licensing of SAS® Analytics because they are implemented with independent engine. SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Free Windows Admin Tool Kit Click here and download it now
May 3rd, 2011 3:26pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics