This creates the EditorBasics project in the Solution Explorer as shown below. It comes with the folders, Data Sources, Data Source Views and SSIS packages. You could further expand this node to see its contents. By right clicking this node, you reveal the drop-down menu from which you can do a number of things. Click on the New SSIS Package menu item.
You could also create a new package as shown in Figure 5. This figure shows other details for the Package1.dtsx [Design] tab as well.
Figure 5
The Package consists of the following: Control Flow, Data Flow, Event Handlers, and Package Explorer. More items may show up during package development. Please read the grayed text Control Flow of the package, this explains how to configure this part. The pane in the bottom is where the connections are placed called the Connection Managers.
Figure 6
The Data Flow page shows the data flow tasks that are needed by the package. You can click on the link to add the Data Flow task(s) as shown below.
Figure 7
In the Event Handlers page, for each task configured you could attach an Event Handler (default OnError).
Figure 8
The Package Explorer is an explorer style list of all items in the package.
Figure 9
The basic steps consist of configuring the Control Flow and the Data Flow pages in the designer. The control flow configuration starts by first creating a table with the same schema as the Oracle 10 XE's "Departments" table using an SQL statement. Once this SQL procedure is created and executed, the table will be created (with no data) in the SQL 2005 Server's SsisEditor database.
The Data Flow is configured by configuring the OLE DB Editors for the two servers. However, for completing the configuration, all information including the tables on the two databases must be specified. And unless the empty table with the same structure as the oracle database is present in the SQL 2005 Server, the specification will not be met.
In order to complete the package, the Control Flow task is executed to create the table in the SQL 2005 Server and then the design of the Data Flow Task is completed. In order not to complicate the procedure this method has been used in the course of this tutorial.
Here is list of preparatory items that are needed for this project to succeed.
Both SQL Server 2005 and Oracle 10G XE should be functioning correctly.
SQL Server 2005 should be properly configured and tested.
Authentication and permissions should be in place for the objects accessed.
An Instance of VS 2005 should be available.
SQL Server 2005 being the destination, the database to which the table will be brought in should be in place by creating an empty database named SsisEditor
The TSQL Script to create a table in SQL 2005 server, which is a copy of the table fromOracle 10G XE, should be available or capable of being created in the IDE. For both Oracle and SQL 2005 server, OLEDB providers will be used.
