topic Re: SAS OLE DB in SQL Server SSIS in Microsoft Integration with SAS

SAS OLE DB in SQL Server SSIS

deleted_user — Tue, 09 Mar 2010 15:25:47 GMT

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.

Re: SAS OLE DB in SQL Server SSIS

Technolero — Tue, 09 Mar 2010 16:15:28 GMT

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

Re: SAS OLE DB in SQL Server SSIS

deleted_user — Tue, 09 Mar 2010 18:03:56 GMT

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 🙂

Re: SAS OLE DB in SQL Server SSIS

LinusH — Mon, 15 Mar 2010 08:56:58 GMT

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

SAS OLE DB in SQL Server SSIS

sirpadk — Wed, 08 Feb 2012 16:57:21 GMT

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