Using SQL Server Analysis Services with Sybase ASE 15

Sybase 15 ships with ADO.net and OLEdb drivers. These come as a part of the Sybase PC client download. Here I describe how you can use Sybase with SQL Server analysis services (SSAS). The following is the software I am using :

• sybase_pcclient_ASE_15.0.2.zip
  
• SQL Server 2008 Analysis services

While installing the PCClient making sure that you select the data access modules. (These were checked by default in my installation but it never hurts to check). Now start the Data Source Administrator:


Click on Add, Fill out the details, And test to see it is working:



Finally click ok and then close the application.

Also (not neccessary but I like to do):
{

• check cursors
• Trasactions tab: Server initiated transactions .... as well as ..... XA Protocol
• Finally set the database name in the connection (Sybase data source administrator) as well as in the Data source(BI Dev studio)

}

Congrats. The difficult part is done :)

Now in BIDS add a new datasource. Click on new to add a data connection. In the connection manager select Sybase OLEDB as your provider as shown:


Fill out the form particularly the Server name as you did in the data source administrator (OSS in my case).



Click on Test connection and you are all set.

Enjoy!

I will try and post about the performance I am getting from my cube some time later on.

Update: For SSIS in OLE DB data source you will need to set "AlwaysUseDefaulCodePage" Custom Property of the OLE DB Source control to True. You can read more here . Also use SQL Queries for data retrieval.

Update2:For SSIS running under SQL Server agent (for scheduling) in case it fails due to "Could not obtain connection" or "Invalid port number" it may be because of one of following resons:

• the drivers may not be accesible to the user that is used to run the SQL Server agent (the service property : log on as). Login as that user. Reinstall the client tools (do a full install) of sybase ASE.
• The package has ProtectionLevel property (SSIS property) as "EncryptAllWithUserKey". This makes connections unavailable to anyone but the developer. Use "EncryptSensitiveWithPassword" which makes it accessible to any domain users that have the password you will be asked to set.
• and Finally: Run all packages as sa (SQL Server agent property). Running them as other users is a complicated process that you can check out AFTER your package has started to work in schedule :)
  

Enjoy!