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
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.
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
The default, Direct Input
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.
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.
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.
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.
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'HR')
EXEC (N'CREATE SCHEMA [HR]')
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)
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).
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
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.
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
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.
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.
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.