Building a SQL Server 2005 Integration Services Package Using Visual Studio 2005
page 1 of 11
Published: 24 Aug 2006
Unedited - Community Contributed
Abstract
The article deals with copying a table on the Oracle 10G XE database to a MS SQL 2005 server by creating a Business Intelligence Project and the Integration Services installed template. This illustrated article may be useful for those who intend to use the Visual Studio 2005 for developing Integration Service Packages.
by Jayaram Krishnaswamy
Feedback
Average Rating: 
Views (Total / Last 10 Days): 52981/ 45

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

 
 

 


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-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 5:26:27 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search