DEVELOPING SSIS in Visual Studio 2005

Introduction

The Import and Export Wizard is easy to use, but it only taps a small part of the functionality of SSIS. To really appreciate the full power of SSIS, you'll need to use BIDS to build an SSIS package. A package is a collection of SSIS objects including:

• Connections to data sources.
• Data flows, which include the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations.
• Control flows, which include tasks and containers that execute when the package runs. You can organize tasks in sequences and in loops.
• Event handlers, which are workflows that runs in response to the events raised by a package, task, or container.

DEVELOPING THE SSIS ORACLE

i am utilizing the power of SSIS to develope a package that will do import from oracle to sql server for one of our projects.

we have a consignement datat stored in and oracle table "ent_imp_cons". for a reason we are not allowing direct access to the table from our .Net application. For that we had a solution of copying the entire data to a SQL Server Database. this task is scheduled to run every x number of minutes.

we have the following tables in our sql server database

• ent_imp_cons_tmp: this is a temporarly table in which data will be saved temporarly
• ent_imp_cons: this table is the orginal table which will have the data from the tmp table if the import succeeded.

our flow is like below:

• Delete Ent_imp_cont_tmp Table (SQL Task)
• Do Oracle Data Import (Data Flow Task)
• Delete ent_imp_cons Table (SQL Task)
• Inserting Records into ent_imp_cons table from ent_imp_cons_tmp table

Get Started

• Open visual studio 2005 and new project --> business Intelligence Projects --> Integrated Services Project.

  

• In the dataflow I have added the following:

  • OLE DB Source which is connecting to the Oracle Database This is the sql statement:
    SQL

    select ref_air_waybill_prefix, air_waybill_number, house_waybill_number, split_reference_number, num_of_packages_expected, num_of_packages_received, num_of_packages_delivered, time_of_status_3,shed_reference_number,oper_airline_code,flight_number,orig_airport_code,actual_time_of_arrival,dest_airport_code,ent_agt_sname from ent_imp_cons

  • Click on the “Show Editor” in the right side. To see the data source properties

    

    

  • Then I add the OLE DB Destination. Connected to sql server. Configure the connection string like the figure below

    

  • Then click on “Mappings” in the left side.

    

• Now in the “Control Flow” have the following controls
  • SQL Task: connected to the Sql Data Source: Note the Connection and SqlStatment properties in the screen shot

    

  • Another SQL Task: connected to the Sql Data Source:

    

  • Another SQL Task: connected to the Sql Data Source:

    using this sql statement: insert into ent_imp_cons select ent_imp_cons_tmp

Resulted Flow Diagram

Deploying the Package

Deploying the package is simple. Just you need to right click on the project solution and build. Provided you have to set CreateDeploymentUtitly to true in the project property page.

In our case, I want to have configuration file also for me to change connection string what I have done is the following:

• In the control flow: right click and select Package Configurations

  

• Click on “Enable package configurations”
• Click “add” button to add new configuration file.
• Select the properties of your controls to allow to be changed.

  

• Now re-build the project.
• Find the deployment folder after you build the project.

  

• To start installing the package click on “SSIS_oracle.SSISDeploymentManifest”
• Follow the installation wizard.
• Install the package in SQL Server.

Notes

To install the package in the sql server you need to have the following:

• Integration service should be installed with sql server.
• To see the installed packages in sql server you need to connect to the integration service.

  

• The packages can be found here:

  

• You can schedule the package in sql job.

References

http://www.accelebrate.com/sql_training/ssis_tutorial.htm

http://decipherinfosys.wordpress.com/2008/09/16/deploying-ssis-packages-in-sql-server-2005/

http://blogs.pragmaticworks.com/devin_knight/2009/06/ssis---creating-a-deployment-manifest.html

http://www.mssqltips.com/tip.asp?tip=1434