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.

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.

Microsoft Bussiness Intelligent solution

Microsoft evolved itself in the industry of "BI" solutions. First edition was attached to SQL Server 2000. it was there for the DTS, Data Transformation Service, and For the OLAP, OnLine Analytical Processing, that make highly definitions of the business requirements. And the Reporting Service solution.


Now, with SQL server 2005. It makes the complete solution. that includes the SSIS, SQL Server Integration Service, it is more complex and good implementation for the ETL solutions, Extract Transform Load. And the SSAS, SQL Server Analysis Serivce, it is the OLAP but with more functionality, and the SSRS, SQL Server Reporting Service, with added features that suits the BI solution not just the Database perspective.

How the things work:

we might raise some questions like, Why all of these? Do I need this solution?

First: the purpose as we mentioned, is to make a business perspective to the data you have in the database. This perspective can be viewed from Top Level Management, Mid Level Management, and Lower level Management.

Second: If you make a solution for some company, person, or anything that deals with huge amount of data, and want to make a decision according to the behavior of the data. Then you need a BI solution.

Now we will say some sort of work flow that every BI solution have it.

First you migrate the data into a database using SSIS, then you need to analyze these data with SSAS, that creates the business definitions, then you need to represent it in something useful to read SSRS, so you are now getting the big picture.

the Next time we will talk the SSIS, features and lakes. and how to solve it.