Optimum "rows per batch" size for Oledb connection in SSIS

In ETL industry, there is a few people know to use "Rows per batch" parameter. The rest of them are not aware of its potential performance improvement capability. The usual habit, developers write 0 (or left it blank) to this parameters area. Which means that every row will be tranfered one by one. If you need to tranfer large table, you will be loosing lots of time.  Some of people  who knows this feature, generally write more than the infrastrature can handle it. If written paramater value more than the infrasture capabilities, large internal/network waits will be seen on monitoring tools while SSIS is preparing the batch.

Thats why, there should be set an optimal value for this parameter. Data is varying during transfer so the value should be in a range of optimal value.

The calculation is very simple math equation.

A- The source datasource
B- The target datasource
C- The location of SSIS package
/
Z- If they are in the same server the limitaion is the writing speed of disk.

It needs to be find out the minimum speed (MS) of A->C or C->B or Z. Also you need to know average size (AS) of one data row. The calculation is should be done in KB size.

(MS / AS) * 0,87 => Below point of range

(MS / AS) * 1,05 => Above point of range

Please share with me your results.

Sql Hello World Function

How to write a sql function on any sql server?

Don't worry about. It is not hard as you think. Just try an example below. Try to understand the keywords.

create function hello_world()
Returns nvarchar(12)
AS
Begin
DECLARE @X nvarchar(12)
set @X='HELLO WORLD!'
return @X
end

You can run this function like:
print hello_world()
or
select hello_world()