In this part of the editor you need to show how the data
flows in the package. The data flows from the source to the destination. It is
therefore necessary to add the two components that provide the connection. The
components can be added from the Toolbox by double clicking
on them when the designer tab is set to Data Flow as
shown in Fig.24. The OLE DB Component icon will appear in the designer pane. For
the present case we need to add two of these controls, one for each source and
destination. Read the tip displayed when you hover over this control. It encapsulates
the data engine which helps in moving the data making required changes.
Configuring Source and Destination Connections
In the Toolbox double click the OLE DB Source control under Data Flow Sources. This adds an OLE DB Source to the
design pane as shown in below.
Figure 24
Right click on the OLE DB source control and from the drop-down
click on the menu item Edit... to open the window.
Figure 25
In the OLE DB connection manager, drop-down
clicking on the arrow shows that it is empty provided there are no existing
connections. Click on the New... button to open the
window Configure OLE DB Connection Manager shown in
the next figure. If there are no existing connections, you will see an empty
window; otherwise, you will see all the existing Ole db connections listed on
the left side panel as shown in Figure 26.
Using an existing Connection
As it happens, in our case there are already a few OLE DB
connections. In fact, there is already a connection for the Oracle 10G XE
database which is highlighted in the configuration window as shown. This is
the data source that we are interested in, the Oracle 10G XE server which is
simply called XE. As you may see, this is using an OraOLEDB
provider, but we want an OLE DB Provider.
On this database server we will be connecting to the HR database and will get a table called "Departments." This package when executed will copy
the "Departments" table to a table in the SsisEditor
database on the SQL 2005 Server.
Figure 26
Making a new connection
What if you are trying to follow the tutorial and you do not
have any existing connections? Well, this is easily accomplished by clicking
on the New... button which opens the next window
shown in Figure 27. Connection Manager shows all the
options for the different OLEDB DB Providers from which you choose the one for
Oracle. The choice that is made for the provider is the Native
OLE DB / Microsoft OLE DB Provider for Oracle.
Figure 27
Type XE for the Server name; XE is
the name by which the Oracle 10G XE is known. Since we will be accessing the HR database, enter the appropriate username/password
combination to access a table on this database, which are hr/hr. When all this is entered, you may test the connection by
hitting the TestConnection... button. This shows
that the connection is OK.
Figure 28
.
Click on the OK button to close the
Connection Manager message and click again on the OK button
to close the Connection Manager window. This will
bring you back to the Configure OLE DB Connection Manager
screen seen earlier, but with the new connection added.
Figure 29
Now when you click on the OK button
in this screen you will see the partially configured OLE DB
Source Editor window shown in Figure 30. Since we will be accessing a
table, the Data Access mode can be Table or View (the
default). There are other options besides this default which you can access
through the drop-down. Now in order to copy the table "Departments,"
we need to click on the drop-down which reveals all the tables in the HR
database. Highlight the "Departments" table and this will become a
part of the source definition.
Figure 30
You may preview the data in this table by clicking on the Preview… button. You may get a warning at this stage about
retrieving the column code page info from the OLE DB Provider as shown in Figure
31.
Figure 31
Go ahead and click on the OK
button. This will bring up the window which shows the data in the "Departments"
table on the HR database as in Figure 32. Some of these operations require the
program to access the "xe" database over the network and may take a
certain amount of time dependent on the amount of data in the table (non instantaneous).
There will be no '"wait" screen showing.
Figure 32
Now, if you highlight the "Column"
on the left-hand side of the OLE DB Source Editor,
you will get the same warning. Go ahead and click on OK to reveal the window
showing the columns in this table.
Figure 33
Clicking on the Error Output item
brings up the next default page. You do not need to make changes here. We will
not be attempting any column transformations. This figure is shown for the
sake of completeness only.
Figure 34
Now click on the OK button in the OLE DB Source
Editor screen to reveal the SSIS Design editor screen. We have not
given a name to this connection, but that can be given by accessing the
Properties window. This window can be displayed by going to the View menu and clicking the appropriate sub-menu item. It
should be found below the Solution Explorer as shown
for the OLE DB Source Data Flow Component just
completed. You should highlight the OLE DB Source Component
to display the window in Figure 35. The Package1.dtsx has been renamed as July18_06.dtsx.
Figure 35
You may change the Name property
in this window to something else that is indicative of its location. We chose Source -OraXe_Departments. The exclamation mark on this
control refers to the same warnings received earlier regarding the code page.
Figure 36
Destination Connection
The destination table is on the SQL Server 2005 in its SsisEditor Database. In this section a connection to the SQL
Server 2005 will be configured. From the toolbox you can double click on the OLE DB Destination control under Data Flow Destinations which
adds this control to the editor's design pane.
Figure 37
Right click the component in the design editor and click on
the Properties to display the Properties
window as shown in Figure 38. Here you need to give a name to the component;
in this tutorial it is "SQL 2K5 Destination."
Figure 38
Editing the Destination Data Flow Component
Right click this component in the design editor and click on
the button Edit... in the drop-down menu. The
program comes back right away cautioning you that the table has no input
columns. Of course, no inputs have been configured yet.
Figure 39
After clicking on the Yes button on
the above Microsoft Visual Studio message screen, you will see the OLE DB Destination Editor screen come up with the OLE DB connection manager: item displaying an existing
connection.
Figure 40
This is not the connection we need; we need a connection to
the SQL 2005 Server. Click on the New... button with
ellipsis to open the window in Figure 41. Here a number of connections,
including the one which was configured for the Oracle XE, are shown. However,
we need a connection to the SQL 2005 Server.
Figure 41
Click on the New... button on this window to open the window
shown below.
Figure 42
The window comes up with the default provider for SQL- Native OLE DB\SQL Native Client. Change this to use the
Microsoft OLE DB Provider for SQL Server. Click on the drop-down to pick a
server which in this case is Hodentek \Mysorian{machine
name\SQL Server instance name}. By default it may come up with the machine name
only (if you accept this, it would not be correct), but make sure that the configured
connection is working correctly by using the TestConnection…
button. For the Mysorian server, the authentication
is a mixed authentication, SQL Server Authentication,
and therefore this option is chosen with the username and password as shown. Place
a check mark on the Save my password box as well. Select
the database into which the table will be copied by clicking on the "select
or enter a database name" button. You should be able to find the SsisEditor database configured earlier. The Connection Manager
will show a successful message after the Test Connection… button has been
tested.
Figure 43
Click on the OK button to get out of the Connection
Manager message and click on the OK button again to close the Connection Manager window. This will bring you back to the
earlier Configure OLE DB Connection Manager window as
shown in Figure 44. You can now see that a new connection has been added to
the collection of connections, Hodentek\Mysorian.SsisEditor.sa.
You can see that your security information has persisted in the connection.
Figure 44
When you click on the OK button you will go back to an even
earlier screen as shown in below.
Figure 45
There are a couple of Data access mode
options, accept the default which gives the fastest loading. Now you need to
look for the table and click on the Name of the table or the
view: drop-down. You will see that there are no tables or views except
for [dbo].[sysdiagrams]. At this point there are no
other options. It is now time to go to the Execute SQL Task
for the source and execute that task which creates the table without the data
in the SQL 2005 Server. The table will only contain the structure. For the
time being cancel the editor; we can comeback to it after creating the table
and look for it.
