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
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