SAS OLE DB in SQL Server SSIS

[deleted_user]

I am having a very hard time finding any documentation or even prove that the SAS OLE DB source in SQL Server SSIS can be used or how it is configured to be used. I'm not sure if this is the right place to ask this, but it was the closest thing to Microsoft that I could find.

I need to use the OLE DB Source and eventually the OLE DB Destination in SSIS and I am getting the following error when I am trying to populate the list of "Tables"

A server error has occurred: ; fn=ydedopn; context =sasej9lib0901.dll; fn = ydedopn; context = sasej9lib0901.dll; fn = ydedopn; context =sasej9lib0901.dll

I have no idea what this means, and nether does Google. if anyone can point me in the correct direction I would greatly appreciate it.

[Technolero]

Are you trying to access SQL Server data from SAS? The following document might help:

Accessing a Microsoft SQL Server Database from SAS on Microsoft Windows
http://support.sas.com/techsup/technote/ts765.pdf

[deleted_user]

Actual I am trying to do the opposite. I am trying to get to SAS data from SQL Server SSIS. Thank you for your time though 🙂

[LinusH]

To access SAS data via OLE DB/ODBC on a server, you need a SAS server session to respond the request. Most commonly a SAS/SHARE server is used. Further more, you need a license named SHARE*NET.
If your SAS data is stored in a SPD Server, you could use the server architecture (and license) within that product.

/Linus

[sirpadk]

Hi

I had the same issue, the solution was to change the datasource property in the connection string to point to the folder where the file is, and not specifically to the file it self.

If you use an SSIS Oledb Source, then just write the folder path in the "Server or file name" and that will work.

I wrote a blog post on this, you can read it here http://sqlserverjedi.wordpress.com/wp-admin/post.php?post=146&action=edit&message=6&postpost=v2

I hope this help you too.

/Rafi