SQL and ML Spaces

Both of these connection managers are NOT mutually exclusive but improper usage can cause data type mismatches with the precision lost.

OLEDB

it supports a wide variety of databases like DB2, Mysql etc.,

It is faster with fast load option set

Connects using OLEDB provider and finds popular usage

OLEDB providers like JET or ACE providers are 32bit only

ADO.NET

Used mostly in for each loop container for looping over a recordset

Lookup components are not supported by this connection

Connects using .net provider

Works best for parameter mapping in the execute SQL task

ADO.NET quite flexible when moving across x86 or x64 environments

No task like OLEDB source component

MS has stronger inclination to support these providers historically

When a SQL environment has an SSIS package with multiple connection managers connecting to multiple databases, it becomes tough to pin point a connection manager in SQL profiler.

So it is advisable to set the property of Application Name within the connection manager properties as below:

get more RAM
run sql server on a dedicated machine with nothing else running on it
dont leave RDP sessions running constantly on SQL Server
DONT start SSMS on the server locally
try to use 64bit version
lock pages in memory access should be there for the service account
Monitor SQL Server:Buffer Manager – Page Life Expectancy (higher is better)
Monitor SQL Server:Buffer Manager – Buffer Cache Hit Ratio(higher is better)

SELECT database_id AS DBID, DB_Name(database_id) AS DBName, file_id, name AS FileName, type_desc,
data_space_id, state_desc, (size *8/1024) AS SizeInMB, max_size, is_media_read_only, is_read_only, is_sparse, is_percent_growth, differential_base_lsn, backup_lsn, physical_name
FROM sys.master_files
ORDER BY type_desc, file_id