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.

SSIS Troubleshoots and Bugs

ah, Now you face some troubles, before we proceed, you have to install SQL Server 2005 SP2. This Service Pack fixes alot of bugs. and also generate another. Anyway, after you install it you might face one or more of the following:

1- Could not add the DataFlow task?

check the SQL Server running service. Open the "Administrative Tools->Services-> SQL Server Integration Services " and change the logon to "Local System Account", then restart the service.

also check that most of the services uses the "Local System Account", not the network account.

this problem occurs when you are in a network, the Sql server uses this account as a general user to work with. But this is wrong

2-Error [OLE DB Destination [2923]]: The column cannot be processed because more than one code page (1252 and 1256) are specified for it.

this happens with the OleDB module that transfers the data into the destination DB.

the problem is the code page is differ, the problem has two solutions:

a. put a "derivative column", and convert all columns code page to the DB server code page.
b. go to the destination [Ole Db Destination] and check "always use default code page" to true.

3- I drawn my transformation but when I replaced it I got error?


A general bug. when you drag a component fromthe toolbox and drop it into the board. It takes a number. if this component generates an elements like the datasources, each element took a number. the problem is this number is unique. and cannot be reallocated to another unit.

the solution redraw the package....

4- Fuzzy lookup somehow took so long and other times does not take time?

the reason is simple, your data contains duplicate rows.

the Fuzzy lookup component get the data, if the data duplicated it builds another indeces. and allocate it to the rows. the do the fuzzy operation. when you try to apply the fuzzy algorithm to duplicated rows, it doubles the effort for ignoring the result of this row values.

Next time for the SSAS

SQL Server Integration Service

So, now we begin talking about the first module of Microsoft BI solution. Simply, we can say that it transforms the data into what you want. It gets the data through at least one data source, and make some operations to it like "lookups", "aggregate", modify columns, and more. then it sets this to a destination, one or more destination.

Microsoft followed the schema that SSIS, devides to two main parts:

1- The Control Flow
2- The Data Flow

The Control Flow:

which contains all things except the data, like: Loops, FTP, File System Task, and more controls that do anything but data.


The Data Flow:

which contains all thing about data, like data sources, and aggregate, and derived columns, and more.

So you can now transform the data.

to easily begin and follow the tutorials see:

1. http://msdn2.microsoft.com/en-us/library/ms170419.aspx
   
2. http://msdn2.microsoft.com/en-us/library/ms170419.aspx
   
3. http://msdn2.microsoft.com/en-us/library/ms170419.aspx
   
4. http://msdn2.microsoft.com/en-us/library/ms167061.aspx
   
5. http://msdn2.microsoft.com/en-us/library/ms166569.aspx

Now, you are aware of the SSIS, concepts and know how to develop a package that transforms the data into something else.

The next time we will explore the bugs and trouble shoots that might face you when implementing a SSIS package.

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.