Systems Engineering and RDBMS

SSIS: Execute SQL Task

Posted by decipherinfosys on March 15, 2008

In one of our previous blog posts, we had covered the basics of SSIS and had also given a comparison between DTS and SSIS. We had also covered Business Intelligence Development Studio (BIDS) and the different features of BIDS. We also discussed about the various design panes available in BIDS. We had also discussed packages and the collection of objects within those packages. In today’s post, we will cover the Execute SQL Task which is one of the control flow tasks.

Before we begin, I want to mention that you can download the code for this post here.

The Execute SQL Task is one of the most important Control Flow tasks. This task is used for almost all database related processing. The Execute SQL task runs SQL/T-SQL statements and stored procedures from a package. It can also be used to run your PL/SQL code for Oracle and likewise for other data sources/destinations i.e. it is not restricted to just running T-SQL and SQL code. The task can contain either a single SQL statement or multiple SQL statements that run sequentially.

Configuring the Execute SQL Task

The image shown below shows the General Page of the task.

es_1.jpg

Some of the important options available for configuration are:

Name, Description:
This option is used to enter the Name of the task and its description. This might be any name depending upon the user’s discretion. Usually they are named based on the operation the task is performing. An example could be: Truncate the input interface Tables.

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

ResultSet:
This option lets us configure the kind of result set the task is returning. The image shown below shows the different kind of result sets which the task can return.

es_2.jpg

The Single row option indicates that the sql statement that the task is executing is returning just a single value. The Full result stores the entire result set returned by the sql statement. The full result set is stored in the variable of type Object. The XML option is used to store the XML result set returned by the sql statement or the stored procedure.

ConnectionType:
SSIS provides us with a variety of connection manager (drivers) to use to connect to the data source/destination. Available connection types include OLEDB, ODBC, ADO, ADO.NET and SQLMOBILE. The image below shows the different connection types available.

es_3.jpg

The most commonly used Connection types are OLEDB and ADO.NET.

Connection:
This specifies the data source we are going to make use of. There are two ways of adding this data source. We can click the drop down on the connection option of the General Page of the Execute SQL task and click New Connection or select from the available connection managers. This is depicted in the following image:

es_41.jpg

When we select the New Connection option, it opens up an editor which will allow us to add a connection manager to the required data source. The following figure shows the editor to add a new data source.

es_5.jpg

On clicking new, it opens up an editor which prompts to enter the server name and the authentication. Another way of adding the data source is through the connection manager which is seen at the bottom in BIDS. Right click on the connection manager pane in the BIDS and click on the kind on connection we wish to make. This opens up the editor described earlier when we created the connection using the Execute SQL task. Once the data source is created, it shows up in the list on Connections in the Execute SQL task as was shown in one of the images above. The image below shows adding of data source using the connection manager pane in BIDS.

es_6.jpg

SQLSourceType

This option allows us to select the source type of the SQL statement that the task runs. We have three options here. The General Page changes depending upon the option that we select here. The options are Direct Input, File Connection and Variable.

With the Direct Input option, the query is directly entered into the Execute SQL task editor. This editor is very similar to the Sql Server Management Studio.

Using the File Connection option, we can specify the file in which we have the sql statements to execute. This will create a connection manager which will point to a location on the hard disk which contains the sql query file.

Using the Variable option, we can point to a variable in the variable collection which holds the sql to be executed. We are going to see these with help of some examples later on.

Using the Execute SQL Task using the Direct Input option for the SQLSourceType:

In this section we are going to look at an example which makes use of the Direct Input option for the SQLSourceType for the Sql Statement Source.

In this example we are going to create a new table. We are going to load this table with some values from the existing table in the AdventureWorks database. In this example we are going to make use of Sales.SalesOrderDetail table in this database.

For convenience, the sql statement for performing this operation is provided here.

CREATE TABLE dbo.SalesOrderDetail(
[SalesOrderID] [int] NOT NULL,
[SalesOrderDetailID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[CarrierTrackingNumber] [nvarchar](25) NULL,
[OrderQty] [smallint] NOT NULL,
[ProductID] [int] NOT NULL,
[SpecialOfferID] [int] NOT NULL,
[UnitPrice] [money] NOT NULL,
[UnitPriceDiscount] [money] NOT NULL CONSTRAINT [DF_SalesOrderDetail_UnitPriceDiscount] DEFAULT ((0.0)),
[LineTotal] AS (isnull(([UnitPrice]*((1.0)-[UnitPriceDiscount]))*[OrderQty],(0.0))),
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF_SalesOrderDetail_rowguid] DEFAULT (newid()),
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_SalesOrderDetail_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED
(
[SalesOrderID] ASC,
[SalesOrderDetailID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

set identity_insert SalesOrderDetail on

INSERT INTO DBO.SalesOrderDetail(SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount,
rowguid,ModifiedDate)
SELECT SalesOrderID,SalesOrderDetailID,CarrierTrackingNumber,OrderQty,
ProductID,SpecialOfferID,UnitPrice,UnitPriceDiscount,rowguid,
ModifiedDate FROM SALES.SALESORDERDETAIL

Let us open up BIDS and create a new SSIS project. Let us name the package as DecipherDuplicateTable. After creating a new package, add the Execute SQL Task on the control flow design pane. Click on the Execute Sql Task once. Then it will allow us to change the name. Let us name it as Duplicate the OrderDetail Table. Double click on the Execute SQL Task to configure it.

In this example we are going to make use of the OLEDB connection to connect to the database. We are also going to make use of the Direct Input option for the SQLSourceType for the Sql Statement Source.

The image below shows the ssis package with the Execute SQL Task ready for configuration.

es_7.jpg

Let us keep the Time Out to be infinite that is let it remain on 0 and since we are not returning any result set let us keep the ResultSet property to None. As described earlier, we are going to make use of OLDEDB connection to make calls to the database. So set the ConnectionType Property to OLE DB. The next step is to create a data source to connect to the database. To do that click on the Connection Property in the General Page of Execute Sql Task. It opens up the editor as was described earlier. Click New on the editor which opens up the editor to enter the Server Name, Authentication and the Database name. The image shown below shows the editor to create a new data source connection.

es_8.jpg

In our example, I am using the server on the local machine, the windows authentication and AdventureWorks Database. After entering all the information hit the Test Connection to test if the connection manager is able to connect to the database. If the connection to the database is successful, the following message is displayed.

es_9.jpg

After creating the connection click on ok to return to the General Page of the Execute Sql Task.

The next step in the process is to define the SQL statement to be executed. As described earlier, we are using the Direct Input option for the Sql Source type Property. After selecting this property, click on the sql statement property which opens an editor similar to that of Sql Server Management Studio as was shown earlier. Then, use the SQL code provided earlier in the post to create the new table and insert the records from an existing table called Sales.SalesOrderDetail.

es_10.jpg

After we enter the sql statement, click on ok and we return to the general page of Execute Sql Task. This completes the configuration of the Execute Sql Task. Click on ok which returns the control back to BIDS. We finished development of our first SSIS package which creates a new table and inserts the rows from an existing table. The image below shows the completed SSIS package.

es_11.jpg

Now press on the little green button on the tool bar of the BIDS to execute this package. Alternatively, we can go to the Debug tab on the menu bar, and click on the Start Debugging or we can also press F5 for debugging. Upon successful execution of the package, we see the Execute Sql Task executable going green.

es_12.jpg

After the successful execution of the package, we can go to the AdventureWorks database and run the following statement to make sure that the table and the data are indeed created.

Select * from dbo.SalesOrderDetail

Configuring the Execute Sql Task to use the Sql Statement from a Variable:

In this section we are going to see how to configure the Execute Sql Task to run a sql statement from a variable. In this section we are also going to see the use of variables in a SSIS package. The configuration is very similar to the earlier section that we had covered before to define the data source. Before going further let us add a variable to the SSIS Package we created earlier.

To add a variable to the package, right click on the SSIS control flow design pane and click on the Variables menu. This will pop up an editor where we can add the variable we require in the package. For our purpose, we are going to add a variable named SqlStatement which is of string type. To add a new variable click on the left most icon on the Variables editor. The editor to add the variables is as shown in the image below.

es_13.jpg

After adding the variable to the package, copy and paste the piece of code provided earlier in the article in the Value column of the Variable. After adding the variable, double click on the Execute Sql Task to reconfigure it to use the sql statement inside this variable.

As described earlier, the configuration is the same until you add the data source to connect to the database. After adding the data source, select the ‘Variable’ option in the SQLSourceType property of Execute Sql Task. After selecting this option, the editor changes a little and prompts us to enter the variable which contains the sql statement in the SourceVariable property. Set the SourceVariable property to point to the variable we just added. This finishes the configuration of the task and let’s us return to the BIDS to execute the package. The image shown below shows the configuration of the task for using the sql statement in the variable.

es_14.jpg

After returning to the BIDS, execute the package by pressing F5 key. Again the Execute Sql Task executable goes green indicating the success of the package. As earlier, we can go back to the database and check if the table and the data were created.

Configuring the Execute Sql Task to use the Sql Script from a File:

In this section we are going to see how to configure the Execute Sql Task to run a sql script from a file. Let us save the script provided earlier in a file and keep it in the C Drive. The configuration is very similar as described in the earlier section up to the defining of the data source.

After adding the data source, select the ‘File Connection’ option in the SQLSourceType property of Execute Sql Task. After selecting this option, the editor changes a little and prompts us to enter the file which contains the sql statement in the FileConnection property. Click on the blank space of the FileConnection property and it will prompt us to create a new file connection or to use any one of the existing file connection. This is very similar to creation of the data source to connect to the sql server. The only difference is that we are creating the source for the sql statement in this case. After clicking on the New Connection a new editor opens which prompts us to enter the file location of the sql file. Follow the instructions to point to the sql file we saved in the C drive. After creation of this file source, we can see the file connection manager to this sql file in the connection manager pane of the BIDS. We can rename these connection managers to reflect the functionality of the connection manager. This finishes the configuration of the task and let us return to the BIDS to execute the package. The image below shows the configuration of the task for using the sql statement in a file.

es_15.jpg

After returning to the BIDS, execute the package by pressing F5 key. Again the Execute Sql Task executable goes green indicating the success of the package. As earlier, we can go back to the database and check if the table and the data were created.

In this post, we went over the configuration of one of the many control flow tasks which the Integration Services offers us. We also saw how to use configure this task to use various kind of sources for the sql statements like the Direct Input, Input from the variable and also the Input from the sql file. In the future posts, we will cover how to use the parameterized queries in the Execute Sql task using the different connection drivers like the OLE DB and the ADO.NET.

One Response to “SSIS: Execute SQL Task”

  1. […] by decipherinfosys on March 26, 2008 In one of our previous blog post, we saw the configuration of one of the many control flow tasks which the Integration Services […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: