Data warehouse from sybase - howto?

Question

Data warehouse from sybase - howto?

CraigIW

SSCrazy

Points: 2301
More actions
April 6, 2010 at 7:29 am

#140148

Hi,
We have a large-ish Sybase database (~150GB) that we would like to synchronise to a SQL Server 2008 database every 30 mins or so. The database has 100 or so tables of interest and some have a few million rows. The target database will then be used for reporting in SQL Server.
What tools can I use to keep snapshoting a Sybase database into SQL Server? The target SQL Server database would need to be available for reporting 9-5 but be updated with newer data several times an hour. I saw the SSIS Transfer Database task, but that does not seem to support non-SQL databases, or provide a way to transfer just the changes.
Any ideas?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

My suggestion would be to build your own SSIS packages. You can use the Import wizard to set a basic package that moves data from Sybase to SQL Server, and then you can customize that to make a query that handles just changes. I assume you know what "changes" are from Sybase.

This wouldn't be a simple SSIS package, but not complicated either. You could probably invest a day or so and get it working well. I'd look at this fantastic article from Andy Leonard

http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/62063/

CraigIW SSCrazy Points: 2301 More actions · Answer 2

Hi Steve,

Thanks for the link. Where is the import wizard that you mention? I do not need to deal with updates, just inserts, so that sounds like a great start.

Thanks.

dumin.net Old Hand Points: 377 More actions · Answer 3

Create a SSIS Package with Dataflowtask , Dataflow task enable you to transfer oledb source to oledbdestination.

Use fastload option to load the data quickly and and increase the networkpacketsize of the connection Manager to utilize the network bandwith .

Regards

Dumin

CraigIW SSCrazy Points: 2301 More actions · Answer 4

Looks like it will need to be one table at a time then eh? It looks like there is actually 600 or so tables 🙁

CraigIW SSCrazy Points: 2301 More actions · Answer 5

This is too big and repetitive to be built visually. Can someone point me in the direction of how I can approach this programatically?

CraigIW SSCrazy Points: 2301 More actions · Answer 6

And the Import/Export wizard does not seem to work with the Sybase drivers.

When I choose the Sybase OLEDB Provider, I then click Properties.. and populate the connection details. When I click Test Connection I get:

An error occurred which the SQL Server Integration Services wizard was not prepared to handle.

Additional Information:

External component has thrown an exception. (interop.msdasc).

If I try it again I get:

The SSIS Runtime object could not be created. Verify that DTS.dll is available and registered. The wizard cannot continue and will terminate.

Additional Information:

External component has thrown an exception. (System.Windows.Forms).

If I try the .Net Provider for ODBC route, I get an 'invalid port number' error.

Any ideas?