SQL Server Integration Services with Oracle Database 10g.docx

SQL Server Integration Services with Oracle Database 10g.docx

《SQL Server Integration Services with Oracle Database 10g.docx》由会员分享，可在线阅读，更多相关《SQL Server Integration Services with Oracle Database 10g.docx（26页珍藏版）》请在冰点文库上搜索。

SQLServerIntegrationServiceswithOracleDatabase10g

SQLServerIntegrationServiceswithOracle Database10g

SQLServerTechnicalArticle

Published:

May 2008

AppliesTo:

SQLServer

Summary:

MicrosoftSQLServer（both32-bitand64-bit）offersbest-ofbreeddataintegrationfacilitiesintheformofSQL ServerIntegrationServices（SSIS）.ThispaperdescribeshowyoucanuseSSIStoeasilyinterfacewithotherdatasourcesrunningontheOracleDatabase 10gdatabaseplatform.

Copyright

TheinformationcontainedinthisdocumentrepresentsthecurrentviewofMicrosoftCorporationontheissuesdiscussedasofthedateofpublication.BecauseMicrosoftmustrespondtochangingmarketconditions,itshouldnotbeinterpretedtobeacommitmentonthepartofMicrosoft,andMicrosoftcannotguaranteetheaccuracyofanyinformationpresentedafterthedateofpublication.

ThisWhitePaperisforinformationalpurposesonly.MICROSOFTMAKESNOWARRANTIES,EXPRESS,IMPLIEDORSTATUTORY,ASTOTHEINFORMATIONINTHISDOCUMENT.

Complyingwithallapplicablecopyrightlawsistheresponsibilityoftheuser.Withoutlimitingtherightsundercopyright,nopartofthisdocumentmaybereproduced,storedinorintroducedintoaretrievalsystem,ortransmittedinanyformorbyanymeans（electronic,mechanical,photocopying,recording,orotherwise）,orforanypurpose,withouttheexpresswrittenpermissionofMicrosoftCorporation.

Microsoftmayhavepatents,patentapplications,trademarks,copyrights,orotherintellectualpropertyrightscoveringsubjectmatterinthisdocument.ExceptasexpresslyprovidedinanywrittenlicenseagreementfromMicrosoft,thefurnishingofthisdocumentdoesnotgiveyouanylicensetothesepatents,trademarks,copyrights,orotherintellectualproperty.

©2008MicrosoftCorporation.Allrightsreserved.

MicrosoftandSQLServerareeitherregisteredtrademarksortrademarksofMicrosoftCorporationintheUnitedStatesand/orothercountries.

Thenamesofactualcompaniesandproductsmentionedhereinmaybethetrademarksoftheirrespectiveowners.

Contents

Introduction1

AboutSSIS1

GettingStarted2

InstallingtheOracleDatabase10gClientSoftware2

TestingtheOracleDatabase10gClientInstallation4

BuildinganETLSolutioninSSIS7

PlanningtheSolution7

CreatingtheSolution7

EnhancingtheSolution13

DataConversion13

DerivedColumns16

Conclusion19

Introduction

ThispaperfocusesontheadvantagesofusingSQL ServerIntegrationServicestoextractdatafromheterogeneoussourcesandimportdataintoMicrosoft®SQL Server™forBusinessIntelligence（BI）analysisandreporting.OracleDatabase 10gdataisusedastheprimarydatasource.

TheaudienceforthispaperincludesITprofessionals,databaseadministrators,andsystemarchitects.ThereadershouldhaveageneralunderstandingofdatabasesandMicrosoftSQL ServerandOracleDatabase 10g.Readersshouldusethereferenceddatabasesontheirpreferredhardwareplatform.

SQLServerispartoftheMicrosoftintegratedBIplatform,andcoversdatawarehousing,analyticsandreporting,scorecarding,planning,andbudgeting.SQL ServerisintheLeader’sQuadrantinbothGartner’sMagicQuadrantforBIPlatformsandMagicQuadrantforDataWarehousing.MicrosoftincludesexcellentBIproductsinbothSQLServerStandardEditionandEnterpriseEdition.TheseincludeSQL ServerIntegrationServices（SSIS）,SQL ServerReportingServices（SSRS）,andSQL ServerAnalysisServices（SSAS）.Incontrast,OracleofferssimilarfunctionalityasanoptionatanadditionalcostwithOracleEnterpriseEdition.ForadetailedcomparisonofthecostofSQL ServerBItoolscomparedwithsimilarOracleproducts,seetheUnderstandingDatabasePricingwhitepaper.

TheprimaryfocusofthispaperisSQL ServerIntegrationServices.SSISprovidessupportforbothheterogeneousandhomogeneousenvironmentsandservesasanintegrationtoolforcustomerswhousemultipledatasourcesandplatformsrunninginMicrosoftandnon-Microsoftsoftwareenvironments.WedemonstratehoweasyitistosetupSSISwithaheterogeneousdatasourceandimportdataintoSQL Server.WealsodocumentthestepstoimportdatafromanOracleDatabase 10gdatasourceintoSQL Server.

ManyITmanagersstrivetoadoptpractical,price-efficientsolutionstosupporttheirbusinessprocesses.Were-emphasizethevaluethatSQL ServeroffersforBIsolutions.SQL ServerincludesexcellentBItoolsatnoadditionalcharge,asignificantvaluethatITmanagerscannotignore.

AboutSSIS

SQLServerIntegrationServices（SSIS）isapremierdatatransformationframeworkbuiltontopofMicrosoftSQL Server.Itperformsawidevarietyoftasksfromsimpleimport/exportoperationstocomplexhigh-performanceextract,transform,load（ETL）tasksbetweenheterogeneousdatasources.Thispowerfulfunctionalitycomesfromatightlyknitsuiteoftoolsmadeupofcontrol-flowanddata-flowlogicdesigners,utilitiestobuildandexecuteself-containedpackages,andtheservicesnecessarytosupporttheexecutionandautomationofhigh-performancedatatransformation.

SSISoriginatedinpreviousversionsofSQL ServerasDataTransformationServices（DTS）—asimpledatatransformationframeworkbuiltwithinSQL Server.WhatDTSlackedinbroadfunctionality,itmadeupforwithasimple“get-it-done”mentality.Thisenableddevelopersanddatabaseadministratorstoextract,transfer,andloaddatabetweenMicrosoftSQL Serverandotherdatabaseplatformsordatasourcesinastraightforwardmanner.However,astheneedsofthedatabasecommunitychanged,sodidtheneedforamorecompleteETLsolutionwithinSQL Server.

SSISistheendresultofyearsofcustomerfeedbackandrefinementbyMicrosoft.ThebasicpremiseofdatatransformationwithinSQL Serverhasnotchanged,butthetoolsandprocessestocompleteETLtaskshavechangedradically.SSIScontainsvastlymorefunctionalitythanitspredecessor.ThisdocumentprovidesapracticalexampleofextractingandtransformingdatafromanexternaldatasourcetoaSQL ServerdatabasebyusingSSIS.ThesourceofthedatainourexampleoriginatesfromanOracleDatabase 10gdatabase.ThisparticularexamplemaybecomemorecommonplaceasthetotalcostofownershipbetweenOracleandSQL Serverincreases.ITdecision-makersrecognizecostasanimportantpurchasecriterionwhendecidingwhichdatabaseplatformstoruntheirbusinessandSQL ServercomeswiththeprogramsnecessaryforbuildingBIsolutions.

GettingStarted

BeforewestartourexampleETLprocess,wemustfirstdefinethecommunicationpathbetweenthesourceOracledatabaseandthedestinationSQL Serverdatabase.ThisrequiresinstallingthenecessaryOraclesupportsoftware.OraclerequiresnetworktransportfacilitiesknownasOracleNettocommunicatewithdatabaseservices.OracleNetisanalogoustotheSQL ServerTabularDataStream（TDS）transportfacility.Themostrecent32-bitand64-bitversionsoftheOracleDatabase 10gclientsoftwareareavailablefordownloadatthefollowinglocations:

Attentionshouldbepaidtotheparticularversionoftheclientsoftwarebeinginstalled—32-bitor64-bit.Installthecorrectversionforyouroperatingsystem（32-bitor64-bit）.

InstallingtheOracleDatabase10gClientSoftware

Afteryoudownloadandextracttheclientsoftwarearchivefile,navigatetothemaindirectorywheretheinstallationfilesarestored,andthenusethefollowingprocedure.

ToinstalltheOracleDatabase10gclientsoftware

1.

Double-clickthefilesetup.exe.ThislaunchestheOracleUniversalInstaller.Awelcomescreensimilartothefollowingfigureappears.SelectCustom,andthenclickNext.

Figure 1

2.Thenextscreenpromptsforahomenameanddirectorypathfortheinstallation.If

necessary,edittheinformation.ClickNexttocontinue.

Figure 2

3.Selectthecomponentstoinstall.Inadditiontothedefaultselections,makesuretoalsoselectOracleWindowsInterfacesandOracleNet.ClickNext.

Figure 3

4.Thenextscreenshowsthestatusofproduct-specificprerequisitechecksastheyareperformed.Makesurethatallcheckssucceedandcorrectanyissuesifnecessary.ClickNext.

Figure 4

5.

AtthepromptfortheportnumberoftheOracleServicesforMicrosoftTransactionServer（Figure 5）,clickNexttoacceptthedefaultport.

Figure 5

6.AsummaryscreenshowsalltaskstheOracleUniversalInstallerwillperform.Verifythattheinformationiscorrect.ClickInstalltobeginthesoftwaredeployment.

7.Afterthesoftwaredeploymentiscomplete,theOracleNetConfigurationAssistantstartsandguidesyouthroughtheprocessofconfiguringtheOracleNetsoftware.Configurethesettingsforyourenvironment.Thefollowingoptionswereusedforthisexample:

Option

Value

PerformTypicalConfiguration

No（unselected）

SelectedNamingMethod

LocalNaming

ServiceName

ORCL

NetworkProtocol

TCP

HostName

ADAMS

PortNumber

1521

PerformTest

Yes

NetServiceName

ORCL

8.WhentheOracleNetConfigurationAssistantcompletes,theUniversalInstallerindicatesthattheinstallationiscomplete.ClickExittoclosetheinstaller.

TestingtheOracleDatabase10gClientInstallation

Aftertheclientsoftwareisinstalled,itiswisetotestandverifytheinstallationbyexecutingabasicdataimportfromOracleintoSQL Server.

TotesttheOracleDatabase10gclientinstallation

9.FromSQLServerManagementStudio,right-clickadatabasethatyoucanusetoperformatestimport,selectTasks,andthenselectImportData.

10.Attheprompttospecifyadat