SQL Server Integration Services with Oracle Database 10g.docx
- 文档编号:14749488
- 上传时间:2023-06-27
- 格式:DOCX
- 页数:26
- 大小:812.65KB
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Server Integration Services with Oracle Database 10g 10
链接地址:https://www.bingdoc.com/p-14749488.html