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 根據(jù)位置，variable要是int
• 用 as 別名

insert

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

就算mapping不行，dml還是可以運(yùn)行

image.png

image.png

image.png

image.png

少了一個(gè)parameter,語(yǔ)法對(duì)了，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