Execute SQL Task

• Execute SQL task runs SQL statements or stored procedures from a package.

• Execute SQL task can be used for:
  ▸ Create, alter, and drop database objects
  ▸ Truncate a table or view in preparation for inserting data
  ▸ Re-create fact and dimension tables before data loading
  ▸ Run stored procedures
  ▸ Save the rowset returned from a query into a variable

• Supported Connection Managers are:
  ▸ Excel
  ▸ OLEDB
  ▸ ODBC
  ▸ ADO and ADO.NET
  ▸ SQLMOBILE

• Source of the SQL statements used by this task can be:
  ▸ Direct: SQL Statement can be specified directly
  ▸ Variable: A Variable containing SQL Statement
  ▸ File Connection: Connection to a SQL File using file connection manager

• Depending on the type of SQL statement, result set property can be configured.

GO command can be used to specify multiple statements as a Batch.

It supports Input, Output, and ReturnValue parameter types for Stored Procedures and SQL Statements.

image.png

image.png

map the output

• select name variable 也必须是 name
• select name variable可以是1 根据位置，variable要是int
• 用 as 别名

insert

• insert values(?) ---->parameter mapping: parameter name 一定要写出来位置 0 1

就算mapping不行，dml还是可以运行

image.png

image.png

image.png

image.png

少了一个parameter,语法对了，mapping少也是可以run的

• when the first select statement execute, it will not take other select statement. it only can run the first select statement

• execute store procedure, pay attention on output
  if the output is a result set, the variable should be system.object

Execute T-SQL Statement Task

• The Execute T-SQL Statement task runs Transact-SQL statements.

• It supports only the Transact-SQL version of the SQL language and cannot be used to run statements on servers that use other dialects of the SQL language.

• Cannot be used to run parameterized queries, saving query results to Variables or using property expressions.

• Execute T-SQL Statement task can be configured as follows:
  ▸ Specify the connection manager to be used (Supports ADO.Net only)
  ▸ Specify Execution time out.
  ▸ Specify T-SQL Statement.
  ▸ Select Connection Manager and edit it to specify the database to be used.

Execute SQL Vs Execute T-SQL Statement Task:

• Execute T-SQL Statement task supports only ADO.NET.

• Execute T-SQL Statement task cannot be used for:
  ▸ Running parameterized queries
  ▸ Saving the query results to variables
  ▸ Using property expressions

• Execute T-SQL Statement task supports only the Transact-SQL version of the SQL language.

• Execute T-SQL Statement task tasks less memory, parse time, and CPU time than the Execute SQL task.

• t-sql doesn't support pass value

• t-sql can't use parameter

Script Task

• Perform functions that are not available in the built-in tasks and transformations.

• Script task can be used for the following purposes:
  ▸ Access data by using other technologies that are not supported by built-in connection types.
  ▸ Create a package-specific performance counter.
  ▸ Counting number of records in file to initiate data load process.

• Script task uses Microsoft Visual Studio Tools for Applications (VSTA) as the environment for writing and executing Script.

• Script can be written using VB.NET and C#.Net programming languages.

• To run a script, VSTA must be installed on the computer where the package runs.

• You can access external .NET assemblies in scripts by adding references to the assemblies in the project.

• Script task can be configured as follows:
  ▸ Specify the script language.
  ▸ Specify the method in the VSTA project that the Integration Services runtime calls as the entry point into the Script task code.
  ▸ Optionally, provide lists of read-only and read/write variables for use in the script.

• To work with each row of data in a set, you should use the Script component instead of the Script task.

image.png