Large Data Transfer over SQL server DB

Transferring a large data set from server to server or even from database to another on the same server or even from table to table in the same database from SQL server is very big problem concerning time, efficiency, and memory and space together.

I was facing this problem in transferring large data set, contains 1.5 million records per table and each table set is a query from source tables. so the output is transferring query results to a tables. It sounds simple but it is not!

when doing it by SQL import wizard it will take one query per time!!!! how could they think i will need only this!
when trying to do it by SSIS this where i went to hell.... although SSIS has some benefits at least outlining the task flow of the work, but transferring large amount of data is much bigger than it.
my server specs are:
Core 2 due intel processor
6 G.B Memory
win 2008 64 bit
60 GB disk space

It sound OK for transferring 1.6 million records from a query, but it took nearly 1 hour to do so.
Sure I added indices and all the tuning it should take, but with no big improvement.

The best solution I found is: "Bulk insert"...
but how could i dump 1.5 million to a text file quickly as  SSIS also fails doing that. the solution to this problem was bcp utility. "bulk copy" how could not Microsoft integrate it into SSIS or in the import wizard. it is like hiding a treasure.

bcp is brilliant in that, minimizing the time into seconds instead of hours in both dump in files and import into table. the only concern about that was to have a good HDD space to dump files on and then deletes it.

It solved my problem. hooray.

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

this error message has many sources:

• connection is used with user id, and password and not saved
• configuration file does not have the server name
• data flow task is not marked as delayvalidation = true
• low disk space in the server you try to get the data from
• Oracle provider
• Excel 64 provider

I tried to google and found most of the replies are for Oracle and Excel, and sometimes the password. while my case does not include any !!!!!

so I found the delayvalidation and it seemed works but nothing happened. I tried to make the things easier as My package runs in 4 hours and deals with more than 30Million * 200,000 * 1000 records with other tables that compared with the above is look-ups.

what make me confused that it sometimes runs ok, but some other times gives me an error at anytime!!!!

Now, I figured that it may be due to disk space.

SQL Server Reporting Service

Now this is the first tool to view the things, like cubes or even a relational database.

the new version of SQL Reporting service 2005, they added in it 2 features:

1- the report can connect on a cube to build a report in it.
2- the report model concept.

the first one is logical, because they are now building a BI solution. but the second one is fantastic. Although you have a cube in your application or not, Although you meet all the reporting requirements or not. The Client can do his report by him self. and can store it in warehouse to view it multiple times!!!!

The report model make this task easy.

How?

First you generate the Model, from either cube or RDB. Then deploy it. then link the client to the report server. He will find a link named "Report Builder". The wizard will come now.

The Client will open the report builder, selecting the appropriate model. then tada.. he will build a report of huge functionality like navigate through, complex calculations, grouping, ...etc without even writing any minor script. Just drag and drop.

the report model is the business representation of your entities. whether it is cube or RDB. it will generate a business entities that you can change their names to logical names example: instead of "PRODUCT_CATEGORY", you will rename it to: "Product Category".

The Client will generate all his reports and then deploy it in the report server. Then you can link to it from the report you are doing or from your application.

try it by your self, it is cute....

the next time we will apply security on the report and show some troubleshoots.