I’m data mining with SQL Server 7.0 Data Transformation Services (DTS). When I try to run queries to return a rowset in Visual Basic (VB) stored procedures or the Query Analyzer, I continually receive the error message
Server: Msg 7350, Level 16, State 2, Line 1 Could not get the column information from the OLE DB provider 'dtspackagedso'.
The Decision Support Objects (DSO) rowset provider is enabled in the Workflow properties. Also, the command line that uses dtsrun executes without error.
I’m using the statement method and the DTS package stored in SQL Server with the query
SELECT * FROM openrowset
Finally, I tried to set DTS to a file and use the globally unique ID (GUID), but I still can’t retrieve a rowset. Does the package work when this step isn’t acting as a DSO rowset provider—for example, testing the package against a dummy destination with DSO switched off? I added the package as a linked server. I can successfully connect by using the ODBC drivers with the Import and Export wizard to bulk-import data and add a connection to a DTS Package. However, I continually experience connection problems when I try various methods of linking to the server.
If you’re saying you can’t access data by using the DTS package as a data source (using OLE DB/DSO functionality), and that the package can access the data, the various techniques of accessing data through the OLE DB provider will work fine. However, accessing data doesn’t work when the DSO capability is turned on, which is required to use the package as one data source to another. Your problem is likely in the OLE DB provider driver conformance.
DSO is a DTS package’s ability to act as an OLE DB provider, which lets the user encapsulate data extraction, transformation, and loading (ETL) logic (key components of data warehousing) automatically in a package and execute it. DTS is susceptible to the compliance level of OLE DB providers. Microsoft relies on many optional interfaces that not all providers support. An OLE DB programmers reference I checked identifies interfaces that need support at different levels of compliance. Level 0 Conformance interfaces need support. This reference also identifies other interfaces that DTS requires for higher levels of conformance.
Continual connection problems when you try to link to the server are a symptom of a noncompliant OLE DB provider because DTS requires more functionality than linked servers do. You need an updated OLE DB provider tested at a higher level of conformance to work with DTS correctly.
The first way I attempted to access the server was through our primary data store, a Centura SQL Server 6.5 OLE DB provider (SQLBASEOLEDB). When I attempted to access the tables in the Enterprise Manager, I received the error
A 00441 SQLBASE internal database connect failed
So I called DLLHOST.exe and pushed the CPU usage to 99 percent continuously for that application. However, I suspect the machine will crash if I leave it this way. Does TechNet list a bug defining a problem with this application?
I searched TechNet and the Knowledge Base and found no bug listing about accessing the tables in the Enterprise Manager this way.
The second way I attempted to access the server was with the OLE DB provider for ODBC. I received a similar connection problem.
Running the code using the DTS package as a data source by specifying the DSO rowset provider as a data source property, I tried the query
SELECT * FROM openrowset ('dtspackagedso','/Usa /P /Saus6 /Ntest','SELECT * FROM DTSStep_DTSExecuteSQLTask_1')
Can I execute this query in the Query Analyzer in its current form and receive a rowset? The task is a simple select query with a connection to another database. The package works when the step isn’t acting as a rowset provider.
DTS has only one step for testing purposes. If I set up DTS as a standalone application in VBScript, with no external access through the VB 6.0 application or the Query Analyzer, it works. However, I want to access the rowset that the DTS package might select from an external source. How can I leverage the connections to the Centura SQLBase Server and any other servers and run queries? I couldn’t get the linking functions to work and I attempted to resolve the OLE DB problem with Centura. I have successfully linked to other SQL Servers (using the OLE DB for ODBC).
You need to specify the step/task only if more than one exported rowset provider is in a package, so the last parameter only needs to be SELECT *. Then if you execute this query in its current form in the Query Analyzer, you’ll receive the rowset. However, this is only in the Query Analyzer and not in DTS without a newer OLE DB provider.