The Execute Sql Task Editor consists of a collection of pages.  First of all there is a navigation pane with a list of items on the left side which shows the related page on the right side.  Since we will be using a simple statement with no parameters, we need to work with the item "General."  The default view is already pointing to this item as shown in the above picture.  Also, when the task is executed we will not be expecting to retrieve any result sets nor will we be using any expressions.

Pertaining to the item, General, there are a number of items that need to be taken care of.  First of all, a Name has to be provided by which the component will be known to the system.  The Description can be anything that describes the task.  The Timeout can be specified, but a value 0 means it waits for a long enough time.  The CodePage 1252 refers to the Western European (windows) encoding name.  There will not any ResultSet expected of this package.  The Connection Type can be chosen from a list that drops-down when you click in a clear area as shown below.

Figure 13

Since we will be making OLEDB connections the default, OLE DB will be used.  The SQL Source type can be one of three types which can be accessed by the drop-down by clicking on the related empty textbox along side of it.

·         The default, Direct Input

·         File Connection

·         Variable

Each of the above options requires a Transact SQL Statement to be provided through directly providing a statement, using a file reference to the SQL statement or through a variable defining the SQL statement.

It is also necessary to establish the connections to the source and destination as well as provide the statement to be executed.

Configuring the Execute SQL Task

Right click the Execute SQL Task to bring up the Execute SQL Task Editor shown in Figure 14.  The name "Preparation 2k5 SQL Task" was chosen for this task and a meaningful description was chosen - "Create a 'Departments" table in 2k5 database, SsisEditor.  By clicking on an empty area in the textbox corresponding to the connection, a drop-down menu appears from which the connection can be chosen.  The details of this connection are described in the "Data Flow" section to follow.  Choosing the Hodentek\MYSORiAN.SsisEditor.sa1 connection allows the editor to appear.

Figure 14

We still need to configure the SQL Statement that will create a table with the meta data of the "Departments" tables in the Oracle 10G Xe database, since we are making a direct input into the SQL 2005 Server database's table.  By clicking in the textbox along side, SQL Statement item makes the Build Query… button active.  Click on the Build Query… button to open the SQL Server Login as shown in the Figure 15.  Of course to access resources on the server, authentication will be needed.

Figure 15

When you enter the password and click OK to this screen, the Query Builder window opens up where a SQL query can be fashioned by adding tables and using this interface.

Figure 16

.

When you right click on an empty pane (all are empty to start with) you can choose Add Table from the drop-down menu.  Since the SsisEditor database is newly created, it has no user created table.  We need to create a SQL statement that would create a table in the SsisEditor database; a table which has the same meta-data as the table "Departments" we are copying over from Oracle 10g Xe.  Please refer to the table structure presented earlier.  An appropriate Create Table statement is the following.

Listing.1

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'HR')

BEGIN

EXEC (N'CREATE SCHEMA [HR]')

END

CREATE TABLE [SsisEditor].[HR].[DEPARTMENTS] (

[DEPARTMENT_ID] NUMERIC (4,0) NOT NULL,

[DEPARTMENT_NAME] varchar (30) NOT NULL,

[MANAGER_ID] NUMERIC (6,0),

[LOCATION_ID] NUMERIC (4,0)

)

GO

Enter this in the Query Builder's SQL area.  You may also type the statement directly into the text box and the click on the Parse Query button to verify the syntax as shown in Figure 17 (where two windows have been superposed).

Figure 17

The Query Builder utility that you can access from Execute SQL Task Editor can be used only for SQL Queries and not for creating objects.  Query Builder does not support creating a table.

When you click OK to this screen, you will have completed configuring this task.  It may be prudent to document the properties of this task.  The window in Figure 18 shows the properties window of this Execute SQL Task.

Figure 18

Adding a Data Flow Task

After the data is ready it should call a data flow task.  You may add a data flow task from the toolbox.  To add this control to the design pane, place your cursor in the design pane with the active tab set to Control Flow.  Next, double click the Data Flow Task component in the Toolbox. This adds the component to the design pane.

Figure 19

Right click the Data Flow Component just added (screen shot above is before adding the component) to open the Properties window.  Provide a name for it by typing it in the Properties window.  Here in it is called Ora_2k5.  The items you need to change are: make the FailPackageOnfailure "true," the default is "false" and change DelayValidation from the default "false" to "true."

Figure 20

Now the design pane has the two tasks as shown in Figure 21.  Now the Data Flow Task logically follows the Control Flow Task and a constraint should be added to make this happen.

Figure 21

Add a Constraint

If several events are taking place you want to exercise control over the order the events should happen, as well as under what conditions the event can take place.  The data flow must take place only after the table is created and this is a constraint for the data to flow.  You can add a constraint from the drop down menu item when you right click the Execute SQL Task or the Data Flow Task.  When you click on the Prepare SQL 2K5 task and right click to add a Precedence Constraint, you will see the Control Flow window displayed.

Figure 22

It comes up with the default From: and from the dropdown you can make To: "ora_2k5" as shown in the above picture.

Now click on the button OK in the above screen and you should see the window in Figure 23 displayed.

When the Prepare 2k5 SQL Task is executed, the "Departments" table should appear in the SsisEditor database of SQL 2005 Server.  We will defer it until a little later.

Figure 23