by Raju Maharjan
12. November 2010 02:04
Bulk Insert Task was the most preferred way to do large bulk loads in DTS because it takes advantage of the performance gains using the T-SQL BULK INSERT command. With Integration Services, however, it is recommended to use the Data Flow Task. The Data Flow Task has numerous advantages over the Bulk Insert Task, which has rigid limitations on how it can consume and insert data. The Data Flow Task can bulk load data with comparable performance, especially if using the SQL Server Destination Adapter, while providing much more flexibility.
The Bulk Insert Task is a wrapper task around the T-SQL Bulk Insert statement. The task generates the Bulk Insert statement from the properties set on the task and then sends it to the server where it will be executed. Therefore, all the limitations that apply to the Bulk Insert T-SQL statement also apply to the Bulk Insert Task.
Because the Data Flow Task replaces the features provided by the Bulk Insert Task, it is recommended to use the Data Flow Task for bulk-loading scenarios. The Data Flow Task is easier to set up, just as performant for straight bulk inserts, and more flexible.
by Raju Maharjan
10. November 2010 22:56
The Execute SQL task runs both SQL statements and/or stored procedures from a package. The task can contain either a single SQL statement or multiple SQL statements that run sequentially. You can use the Execute SQL task for the following purposes:
-
Truncate a table or view in preparation for inserting data.
-
Create, alter, and drop database objects such as tables and views.
-
Re-create fact and dimension tables before loading data into them.
-
Run stored procedures.
-
Save the rowset returned from a query into a variable.
You can configure the Execute SQL task in the following ways:
-
Specify the type of connection manager to use to connect to a database.
-
Specify the type of result set that the SQL statement returns.
-
Specify a time-out for the SQL statements.
-
Specify the source of the SQL statement.
-
Indicate whether the task skips the prepare phase for the SQL statement.
-
If you use the ADO connection type, you must indicate whether the SQL statement is a stored procedure. For other connection types, this property is read-only and its value is always false.
The Execute SQL task can be used in combination with the Foreach Loop and For Loop containers to run multiple SQL statements. These containers implement repeating control flows in a package and they can run the Execute SQL task repeatedly. For example, using the Foreach Loop container, a package can enumerate files in a folder and run an Execute SQL task repeatedly to execute the SQL statement stored in each file.
Subscribe