I am trying to export data from Cassandra to SQL Server using SSIS. I'm using a Cassandra ODBC driver and the table only have 5 fields.
A test table in Cassandra with 50 000 rows takes about 1 sec to import, works perfect. The production table has about 1 billion rows. I use the same setup Cassandra odbc as source, Sql server as destination. Fast Load for bulk insert. It took 5 1/2 hours and it said it finished without errors, The problem, nothing was stored in the Sql Server table.
Shouldn't it be inserting data all the time during the export-import? Nothing happens and the table is empty the whole time.
are you sure you are looking at the right database and table
could it be that it is still writing to the table
what state is the database in. maybe something did fail and its rolling back
Are you using the OLEDB Destination - or SQL Server Destination? If the latter - that is really only used for SQL Server -> SQL Server migrations on the same server.
Using the OLEDB Destination - you can control the batch and commit sizes. Use that for your destination and set the batch/commit sizes to 2,000,000 (with the fast load option). This will almost certainly run a lot faster and will not bloat the transaction log.