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.

Microsoft Analysis Service Performance Final

Final Show of this series, Microsoft does not have a solution for the Large column data size!!!!!! they offer to upgrade the system rather than having a solution.

anyway, the solution is to crack down the dimension so that SSAS can handle it!!!!!!

MS SSIS 2005

The component failed because truncation occurred, and the truncation row disposition on "output column " specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

common error messages when dealing with SSIS. and you google alot to know the answer. some people advise to user derived columns!

but the problem is when initiating the data source itself.

So the answer is pretty simple:

for the field that produce this error, on the output field for this field make it not fail the componenet but rather ignor this error.

SQL Server Analysis Server Exploring the studio and troubleshoot

Now we made a cube that wrap all the dimensions and facts we want to analyze in our scope.

The First tab when clicking on the cube is the "cube structure", where you can see the cube structure and modify the relationships and dimensions the cover the cube from the business perspective. you can add a hierarchy, or delete it. you can add new measure, new dimension from the data source view.

The "Dimension usage" tab. Where you can link those dimensions with measures.

The "Calculations" tab . where you can add new calculations to help you to more analyze the data.

The "KPI" tab . where you can add your indicators to see if the data is going fine or there exists something wrong. here you have to know some MDX query language.

The "Perspectives" tab. where you can add perspectives to the cube on each one you will specify the dimensions and the measures related to it.

The Last tab is the "Browse" tab. where you can browse the cube. to do it you have to deploy the cube, and to do so. you have to right click on the project name->click on "properties"-> then change the path of the deployment server. then click ok. then click deploy.

Clarification:

After making the KPI's you may want to see them. There are many tools enable you to browse the cube and manipulate it like: Excel, ProClarity owned by Microsoft now, business Object.

in all of those you may not see the KPI's as you saw them in the Visual studio. Why? because this is an owned shapes by visual studio, you may see this KPI's with different shapes and colors. so according to the viewer, you will design the KPI indicator shape to fit the viewer. Otherwise you have to develop this viewer to the target tool.

Troubleshoot:

"A connection cannot be made. Ensure that the server is running. "

this error message occurred for the impersonation of the SQL server instance.
the solution is:
1- in the project properties, deployment section, modify the server to the SQL server 2005 instance.
2- in the data source double click, in the impersonation tab, select the "use the service account".

Now you have ended the cube and we wanna view it.
The next time we will explore the Reporting Service structure.