Building a SQL Server 2005 Integration Services Package Using Visual Studio 2005
page 7 of 11
by Jayaram Krishnaswamy
Feedback
Average Rating: 
Views (Total / Last 10 Days): 113752/ 216

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.

 


View Entire Article

User Comments

Title: Question   
Name: Ram
Date: 2012-02-16 2:22:59 PM
Comment:
I have installed Visual Studio 2005, but when I open Visual studio, I am unable to see the template 'Integration Services Project'. Any ideas?
Title: Big brother   
Name: Hussein Negm
Date: 2011-05-09 6:04:31 AM
Comment:
Thanks a lot man! this article as roob said is a big help and the detailed explanation really helped a lot to begin using this tool. I wish you'd do more tutorials for more advanced capabilities :)
Title: Just the right article!   
Name: Roob
Date: 2010-04-22 11:55:25 AM
Comment:
This article helps remove the fear of the unknown for those experienced in SQL < 2005 that are yet to make the change.
Title: Building a SQL Server 2005 Integration Services Package Using Visual Studio 2005   
Name: Ram
Date: 2009-10-23 9:26:41 AM
Comment:
very nice
for a beginner very nice step to teaching
Title: Building a SQL Server 2005 Integration Services Package Using Visual Studio 2005   
Name: Jayaram Krishnaswamy
Date: 2009-07-15 12:33:32 PM
Comment:
If you liked this article you may want to read to of my books written in the same style. Links to books and sample chapters are on my URL.

Thanks

Jay
Title: Building a SQL Server 2005 Integration Services Package Using Visual Studio 2005   
Name: Sraswathi
Date: 2009-06-08 3:03:01 AM
Comment:
Very useful article for beginners !

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2018 ASPAlliance.com  |  Page Processed at 2018-05-23 9:02:05 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search