Execute SQL Task in SSIS

Execution Task:-

Execute SQL Task; help us to execute SQL statement from SSIS package. That SQL statement can be a stored procedure or can have single SQL statement as well as multiple SQL statements.

Configuration properties of Execute SQL Task are divided into 4 categories:-

1)    General

2)    Parameter Mapping

3)    Result Set

4)    Expression

To get the configuration properties, right click on execute SQL task and select EDIT.

1)    “GENERAL” tab have below options :-

·         Name: - here we can provide or edit the name of Execute SQL Task. Better to keep the name of Execute SQL Task, generic to functionality.

·         Description: - General details regarding functionality and what going to happen.

·         TimeOut: - Specify the maximum number of seconds the task will run before timing out. A value of 0 indicates an infinite time. The default is 0.

·         CodePage: - A code page, also known as a character set, is a set of 256 uppercase and lowercase letters, numbers, and symbols. The printable characters of the first 128 values are the same for all character set choices. The last 128 characters, sometimes referred to as extended characters, differ from set to set. The code page for SQL Server determines both the types of characters that SQL Server recognizes in your database, and the results that SQL Server returns for queries that involve character comparisons. Here we specify the code page to use when translating Unicode values in variables.

·         ResultSet: - Here we have to specify the result set type, expected by the SQL statement being run. We have four option to chose the type of result set: -

1)    None

2)    Single row

3)    Full result set

4)    XML

·         Connection Type: - Choose the type of connection manager to use to connect to the data source. Here, we have below options: -

1)    OLE DB

2)    EXCEL

3)    ODBC

4)    ADO

5)    ADO.NET

6)    SQLMOBILE

·         Connection: - Here, we have to specify the connection from the defined connection managers.

·         SQLSourceType: - Here we have to specify the source type of the SQL statement that the task runs. Below are the options: -

1)    Direct Input: - In this case, we have to specify the SQL Statement in SQLStatement box.

2)    File Connection: - In this case, we have to specify the file connection in FileConnection box.

3)    Variable: - In this case, we have to specify the source variable (which feed the query) to SourceVariable.

·         IsQueryStoredProcedure: - It confirms, the specified SQL statement is stored procedure or not. This property is editable only if the specify connection is ADO, otherwise the property is read only and default value will be, “False”.

·         BypassPrepare: - This option is available only for OLE DB connection and help us to specify that SQL statement prepared or not. “true” skips preparation, “false” prepares the SQL statement before running it.

Browse: - It helps us to locate a file that contains a SQL statement and copy the contents of the file as a SQL statement into the SQLStatement property

Build Query: - Here, graphical tool help us to create a SQL statement. This option is available only when the SQLSourceType option is set to Direct input.

Parse Query: - Help us to validate the syntax of SQL statement.

2)    “Parameter Mapping” tab have below options: - This dialog box help us to map variables to parameters in SQL statement. Here we have below option:-

·         Variable Name: - Here we have to mention the user or system defined variable.

·         Direction: - To mention the direction of above defined variable as Input/Output/ReturnValue.

·         Data Type: - Define the data type of parameter, to which the value of variable is going to pass.

·         Parameter Name: - Parameter name or position number (depend on connection type) to which the value of variable going to pass.

·         Parameter Size: S size of parameters that have variable length, such as strings and binary fields.

Add: - To add a parameter mapping

Remove: - To remove the parameter mapping from the list.

3)    “Result Set” tab have below options :- It help us to map the result of the SQL statement. Result Set option will be disabled if ResultSet on the General page is set to None. Here we have below option: -

·         Result Name: - If result set type is SINGLE ROW, then we have to specify either the name of a column returned by the query or the number that represents the position of column in the column list of columns returned by the query.

If the result set type is FULL RESULT SET or XML, then we use 0 as the result set name.

·         Variable Name: -Map the result set to a variable by selecting a variable.

ADD: - add a result set mapping.

Remove: - To remove the result set mapping.

To get better understanding of Execute SQL Task with example, you have to go here.