Changes of the Control Flow Tasks in SSIS from SQL 2005 to SQL Server 2014


2005
2008/2008R2
2012/2014
Data Flow Task
-        Runs data flows to extract data, apply column level transformations, and load data.

Data Flow


Data Preparation Tasks
-        Copy files and directories; download files and data; run Web methods; apply operations to XML documents; and profile data for cleansing.



File System
FTP
Web Service
XML
 Data Profiling(added in 08)

Expression added in 2012
Workflow Tasks
-        Communicate with other processes to run packages, run programs or batch files, send and receive messages between packages, send e-mail messages, read Windows Management Instrumentation (WMI) data, and watch for WMI events.

Execute DTS 2000 Package
Execute Package
Execute Process
Message Queue
Send Mail
WMI Data Reader
WMI Event Watcher
For back-compatibility
Removed in 2012
SQL Server Tasks
-        Access, copy, insert, delete, and modify SQL Server objects and data.

Bulk Insert

Execute SQL
Transfer Database
Transfer Error Message
Transfer Jobs
Transfer Logins
Transfer Master Stored Procedures
Transfer SQL Server Objects


CDC Control added in 2012
Scripting Tasks
-        Extend package functionality by using scripts.

ActiveX Script
Script
 For back-compatibility
Removed in 2012
Analysis Service Tasks
-        Create, modify, delete, and process Analysis Services objects.

Analysis Services Execute DDL
Analysis Services Processing
Data Mining


Maintenance Tasks
-        Perform administrative functions such as backing up and shrinking SQL Server databases, rebuilding and reorganizing indexes, and running SQL Server Agent jobs.

Backup Database
Check Data Integrity
Execute SQL Server Agent Jobs
Execute T-SQL Statement
History Cleanup
Notify operator

Rebuild index
Reorganize index
Shrink Database
Update Statistics






Maintenance Cleanup added in 08

Custom Tasks
-        Write custom tasks using a programming language that supports COM, such as Visual Basic, or a .NET programming language, such as C#.