AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=947&pId=-1
Building a SQL Server 2005 Integration Services Package Using Visual Studio 2005
page
by Jayaram Krishnaswamy
Feedback
Average Rating: 
Views (Total / Last 10 Days): 103383/ 242

Introduction

Alternate Title: Copying an Oracle 10G XE Table to SQL 2005 Using VS 2005 and SSIS

Data Transformation Services (DTS) which came with MS SQL Server 7.0 has now emerged as SQL Server Integration Services in MS SQL 2005 Server, an enterprise quality ETL (extraction, transformation, and load) program.  DTS as implemented in SQL 2000 was limited in its tasks and transformations.  The graphic user interface gave the package the required functionality by integrating workflow process with the integrate dataflow.  The Control and Script support by VBA were also limited.

SSIS is now a full blown service offering a complete solution featuring extensibility, a .Net interface to designing packages, a great number of tasks, transformations, logic, security, management, and improved deployment.  As you will see in this tutorial, you will encounter a multitude of wizards to get you to the intended goal.  However, going from DTS to SSIS for designing packages requires skills that may take time to get used to.  This tutorial is written in a minimalist style so that you will not be taken through hyper jumps unless that is absolutely needed.

Scope of this tutorial

Using the VS 2005 IDE a Business Intelligence Project will be created.  Using an Integration Services Project template, a single table from the Oracle 10G XE database will be copied to a database on the SQL 2005 Server which will be created by an SSIS task.  This step-by-step tutorial shows you how this may be achieved.

The Oracle 10G XE server, the MS SQL2005 server and the Visual Studio 2005 are all on the same machine running on the XP Professional Media Center Edition operating system.

Figure 1 shows the "Departments" table on the HR database in Oracle 10G XE that will be copied to the SsisEditor database created for this exercise on the MS SQL 2005 Server.  This table has 27 rows and 4 columns.

Figure 1

 
 

 

SQL DTS Flashback

In order to see how differently SQL Server Integration Services accomplishes the same task, it will be helpful to see how it was done with DTS.  Some of the DTS concepts are the same in SSIS.  Using a connection to SQL 2000 Server, an ODBC connection to Oracle 10G XE server and a Transform Data Task are all needed to accomplish the task in DTS.  Using these components, the copying process can be designed into a DTS Package as shown in Figure 2.  Although Transform Data Task has a limited repertoire, it does copy tables admirably well.  It even has a "Copy Table" Transform Data Task which is not seen in the Visual Studio 2005 IDE.  Thus, this flashback may not be comparing apples with apples, but it at least shows that the underlying concepts are not radically different.

Figure 2

 
 

Creating a SSIS Project and adding a Package

The starting point in creating a package is to create a Business Intelligence Project using the Integration Services Project - a standard Visual Studio 2005 installed template as shown in Figure 3.  It has been given a name here, Editor Basics.

Figure 3

 

 

 

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.

Figure 4

 

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

 

Overview of this tutorial

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 from
Oracle 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.

Control Flow Design

An Execute SQL Task is a control flow item which can be chosen from the Control Flow tasks in the Visual Studio IDE's Toolbox as shown in Figure 10.  You can see the large number of tasks added in SSIS (Compare to those in DTS, Figure 1).  In the next section a task is added.

Figure 10

 

 

Add an Execute SQL Task

From the task list click and draw an Execute SQL Task on the design pane of the editor with the tab Control Flow chosen as shown below.  When you right click on this component you can access the drop-down menu from which various items can be accessed or performed.

Figure 11

 

Edit the SQL Task

Click on the Edit...menu item to open the window.

Figure 12

 

Execute SQL Task Editor Details

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

 

Data Flow Details

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.

 

Executing the SQL to create a table in SQL 2005

Change the designer tab to "Control Flow" and right click the Prepare 2k5 SQL Task which brings up this drop-down menu.

Figure 46

 

 

From the drop-down, click on "Execute Task."  Make sure you only click on this particular control.  The program runs and you should see a display as shown in Figure 47.  Green shows it ran OK and red means it ran with errors.

Figure 47

 

 

You can also get an idea of how the Execute SQL Task executes when you tab to Progress with the green arrow on its left.  It takes under 02. Seconds to run the Create Table query.

Figure 48

 

 

 

 

 

 

 

 

Before executing the query, the SsisEditor database had only the system tables.

Figure 49

 

After the query execution, the HR.Departments table was created as shown below.

Figure 50

 

The table has only a structure at this point.

Figure 51

 


 

Complete Configuring the OLE DB Source

Earlier we stopped short of completing the OLE DB Destination Component.  Now that a HR.DEPARTMENTS table has been created, the OLE DB Destination Data Flow configuration can be completed.  When you right click on SQL2k5 Destination Data Flow Component and then click on Edit…menu item you will see the window shown in Figure 52.

Figure 52

 

Click on the Microsoft Visual Studio message's Yes button to open the Configure OLE DB Destination Editor.  Now from the drop-down you may click and choose the HR.DEPARTMENTS table.

Figure 53

 

.

 

When you click on "Mappings" in the Left-hand side you will see the window shown in Figure 54 since we have not yet established a path for the data flow.  The source and destination are disjointed.

Figure 54

 

 

Add a Path for dataflow

The path from Source to Destination may be created by clicking on the Add Path drop-down menu item.  When you right click the Source Data Flow Component, as shown in Figure 55, you will get access to the drop-down menu.

Figure 55

 

This brings up the Data Flow window with the "From:" showing the Source and the SQL2k5 Destination can be set from the "To:" drop-down as shown in Figure 56.

Figure 56

 

When you click on the OK button on the Data Flow window you will see the window in Figure 57.  From here you need to choose the appropriate output.

Figure 57

From the two outputs choose the correct one for mapping.  The OLE DB Source output (from Oracle) will be connected to the OLE DB Destination input (SQL 2005 Server table).

Figure 58

 

When you click on the OK button on the above window, the path for data flow will be set.  This should practically complete the package.

 

Building the Project and Executing the Package

Now build the project and execute the package using the drop-down menu by right clicking the package in the Project explorer.  After the execution is complete, various items may be verified.  The design window shows that the package executed and 27 rows were copied.

Figure 59

After the execution of the Package, you may verify that the table "Departments" is copied over to the SsisEditor's database in the MS SQL 2005 Server.

Clicking on the Package Explorer tab on the previous figure shows the contents in Figure 60 with completely expanded nodes of the objects designed.

 

Figure 60

Again, clicking on the Progress button allows us to see its contents.

 

Figure 61

 

 

The Debug message at the end of the execution comes with a number of steps in the execution as shown in Figure 62.

Figure 62

 

The message content for one of the runs is as follows:

SSIS package "July18_06.dtsx" starting.

Information: 0x4004300A at Ora_2k5, DTS.Pipeline: Validation phase is beginning.

Warning: 0x80202066 at Ora_2k5, Source -OraXe_Departments [1]: Cannot retrieve the column code page info from the OLE DB provider.  If the component supports the "DefaultCodePage" property, the code page from that property will be used.  Change the value of the property if the current string code page values are incorrect.  If the component does not support the property, the code page from the component's locale ID will be used.

Information: 0x40043006 at Ora_2k5, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Ora_2k5, DTS.Pipeline: Pre-Execute phase is beginning.

Warning: 0x80202066 at Ora_2k5, Source -OraXe_Departments [1]: Cannot retrieve the column code page info from the OLE DB provider.  If the component supports the "DefaultCodePage" property, the code page from that property will be used.  Change the value of the property if the current string code page values are incorrect.  If the component does not support the property, the code page from the component's locale ID will be used.

Information: 0x4004300C at Ora_2k5, DTS.Pipeline: Execute phase is beginning.

Information: 0x402090DF at Ora_2k5, SQL 2k5 Destination [58]: The final commit for the data insertion has started.

Information: 0x402090E0 at Ora_2k5, SQL 2k5 Destination [58]: The final commit for the data insertion has ended.

Information: 0x40043008 at Ora_2k5, DTS.Pipeline: Post Execute phase is beginning.

Information: 0x40043009 at Ora_2k5, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Ora_2k5, DTS.Pipeline: "component "SQL 2k5 Destination" (58)" wrote 27 rows.

SSIS package "July18_06.dtsx" finished: Success.

The program '[3788] July18_06.dtsx: DTS' has exited with code 0 (0x0).

 

The package was renamed as July18_06.dtsx and it is located at "c:\documents and settings\jay\my documents\visual studio 2005\projects\editorbasics\editorbasics\July18_06.dtsx.

Finally, the contents of the table copied to the MS SQl 2005 Server are shown below.

Figure 63

 

Summary

This step-by-step tutorial has shown how to use the Visual Studio 2005 IDE to create a MS SQL 2005 Server Integration Services Package.  DTS in SQL 2000 has its own design editor and it is not integrated with the Visual Studio 2003.  Packages created using the Import/Export Wizard can now be modified in the VS 2005 IDE whereas this was not possible in DTS.  Again, the RAD capability is enhanced with more controls.  However, more controls also imply an increase in the steepness of learning.  For copying and loading data the Native OLE DB providers are recommended.  As a comparison, the DTS example shown earlier used an ODBC connection to Oracle which adds its own overhead.  The package created showed a simplified scheme where an Execute SQL statement was first executed followed by an execution of the package.  As this is intended to be a basic tutorial, the focus was always maintained to describe the interface in as much detail as possible.  The readers may benefit by reading several of my DTS related articles, the links to which can be found at my web site under tutorials.  I assumed that the reader knows how to create a new database in SQL 2005 Server but, in case he/she does not they can look up this link.  I strongly urge the readers who may want to expand their DTS designer experience to review those articles.


Product Spotlight
Product Spotlight 

©Copyright 1998-2018 ASPAlliance.com  |  Page Processed at 2018-10-19 4:17:31 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search