Hi TechNet!I am in a pinch and need to find an answer to report to my CEO within the hour. So forgive me if I could've found the answer with more digging. Does anyone know if there is a known provider to import SAS (as in "SAS Institute") datasets into SQL Server with SSIS? I know I can export from SAS to a more standard format (e.g. CSV or other delimited flat file type) but I don't want to waste a potential opportunity to tout SQL Server features. Thanks in advance for any help you can provide.

You can use SAS Local Data Provider (can be downloaded separately and comes as part of SAS for Windows installation). http://support.sas.com/documentation/tools/oledb/index.htm

Hi, I tried the use of the sas share 9.1 driver as oledb source but I can't get data. He's telling me my user/pwd is wrong. It seems the authentification is going wrong. Does anyone has tried to retrieve data from a sas share? Or do I have to put everything in a c# code?thanks in advance ;)

The driver is "driving" me crazy ;)But you can use the script component and use it as input source.here's the code example used to retrieve sas data with sas share.You have to chane ge uid and pwd ;) the commandset to tell him the libname and dataset.don't forget to add columns in the script component to have ouput rows. Public Dim cn As OleDb.OleDbConnection = New OleDb.OleDbConnection() cn.ConnectionString = "Provider=sas.SHAREProvider.9.1; Data Source=sassvpro; Location=uxhadesc020ap.msnet.railb.be;"cn.ConnectionString = cn.ConnectionString() +"User ID=XXXXXXX;"cn.ConnectionString = cn.ConnectionString() +"Password=XXXXXXX;"Try' Open the connection and print the version number.cn.Open()Dim cmd As OleDb.OleDbCommand = cn.CreateCommand() cmd.CommandType = CommandType.TableDirectcmd.CommandText ="<libname>.<dataset>"' Execute the command and get an OleDbDataReader object.Dim reader As OleDb.OleDbDataReader = cmd.ExecuteReader() ' Get the Schema information so we can print out the column names.Dim schema As DataTable = reader.GetSchemaTable()Dim nRows As IntegernRows = 0While reader.Read()Me.MyOutBuffer.AddRow()For i = 1 To reader.FieldCountIf i = 1 Then Me.MyOutBuffer.Column2 = reader.GetValue(i - 1).ToString' If the value is missing then print "-missing-"' otherwise convert the value to a string and print it.'If reader.IsDBNull(i - 1) Then'Console.Write("-missing-")'Else'Console.Write(reader.GetValue(i - 1).ToString())'End IfNextnRows += 1Me.MyOutBuffer.Column1 = nRowsEnd While'Console.WriteLine("Read " & nRows & " rows.")reader.Close()Catch e As Exception Console.WriteLine("Exception: " & e.ToString())End TryIf (Not cn Is Nothing) AndAlso _ (cn.State <> ConnectionState.Closed)Then _ cn.Close()cn =NothingEnd Sub End Class Overrides Sub CreateNewOutputRows() '' Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".' For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".'

Did you get the driver to work?

Hey guys, I have seen this thread many times in my quest to answer the SAS problem of accessing data. It seems that this is the only mention of SSIS and SAS on the web...which is odd to say the least. I am trying to find documentation, or a how to regarding using the OLE DB drive provided with SSIS. Im not sure if I need a server name and a path name...not sure how the "libname" process in SAS applies, or even if it does apply. I am also getting a nasty error msg as follows when i try to list tables in the ole db source stage: Could not retrieve the table information for the connection manager 'sasprod1.###.######'. A server error has occured: ; fn =ydedopn; context = sasj9lib0901.dll. Has anyone successfully used the SAS data provider? and if so can they please give me some pointers? Thanks for your help Jim

"Provider=sas.SHAREProvider.9.1; Data Source=sassvpro; Location=uxhadesc020ap.msnet.railb.be;"Provider is telling you the driver you've installed on your computer where ssis is running or where visual studio is executed.Datasource is is telling you the "server" linked in your services file.location is the dns namep.s. I never reached sas with the ole db but used the scripting

MOJERRY!!! you are the only one on the net that has even come close to helping me...but im not a VB.net guy and I dont understand all of your code.....and some of it seems to be missing, like the Me.MyOutBuffer refference....i have no idea where that comes from. Can you break your code down for us "dummies"? that would be a HUGE help

Also the services file...where is that located?

I got the same problem.

I am still working on the script to pull the data. I hope to have it soon and I will post it. But to answer the services question that file can be located at C:\WINDOWS\system32\drivers\etc on a XP machine.

MoJerry, I am running into an error consistantly and I was wondering if you have seen it. I know that I have access to the table in SAS becuase I can pull back data in PC SAS, but when I run any code I get the following error [Script Component] Information: Exception: System.Data.OleDb.OleDbException: A server error has occurred: ERROR: Libname ERLMF is not assigned.; fn =yhelasg; context =sase9lib0901.dll. at System.Data.OleDb.OleDbCommand.ExecuteTableDirect(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteReader() at ScriptComponent_d9d48e534c3a4d4da6efed31f6c74fbf.ScriptMain.CreateNewOutputRows() in dts://Scripts/ScriptComponent_d9d48e534c3a4d4da6efed31f6c74fbf/ScriptMain:line 46 any thoughts would be great

I used this to talk from SAS into SQL - http://www2.sas.com/proceedings/forum2008/135-2008.pdf. Works nicely. Hopefully you have come right by now!

I'm happy to announce 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/