Here is a direct excerpt from BOL that shows getting data from an Oracle package.
"Issuing Distributed Queries Against Package Data"
You can register a Data Transformation Services (DTS) package as a linked server and issue a distributed query against the package. This capability allows you to consolidate data from diverse sources (for example, from Oracle and DB2 data sources) in a single package, transform that data, and expose the results of the transformed distributed query to any outside data consumer.
To issue a distributed query against package data, you need to define the package as a linked server through the sp_addlinkedserver stored procedure. The following example code illustrates how to use sp_addlinkedserver against a DTS package:sp_addlinkedserver 'DTSOLEDBPkg', 'PackageName', 'DTSPackageDSO', '/FC:\Dts\Dts01.dts')In the sp_addlinkedserver command:
DTSOLEDBPkg is the name of the linked server you want to create.
PackageName is the product name of the OLE DB data source; in this context, you can provide any name or a null string.
DTSPackageDSO is the name of the DTS package OLE DB Provider.
The last argument specifies the location of the file, DTS01.dts. After you have defined the package as a linked server, you can execute distributed queries that include the package as a data source. Following is an example of a distributed query. It performs a join operation on the Orders table in the Northwind database with a package that gets data from a Customer table on an Oracle server. The query assumes the Orders and Customers tables have a common key, which is CustomerID.SELECT a.OrderID, a.CustomerID, a.OrderDate, b.Companyname, b.Region FROM Orders AS a, dtsLink...packageNameOracle AS b WHERE a.CustomerID = b.CustomerIDIn the above query, packageNameOracle is the DTS package name. However, you can use a package step name when multiple steps in a package serve as data sources."
1988-2000 Microsoft Corporation. All Rights Reserved.
For More Information
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on SQL Server Stored Procedures
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.