19 November 2005
Problems in SQL Server 2000 DTS Packages
There are cases when a problem can not be solved with a query, additional data processing being requested. In this category of cases can be useful to use a temporary table or the table data type. Once the logic built you want to export the data using a simple DTS project having as Source the built query. Now the surprises appear: 1. Temporary table Sample: CREATE PROCEDURE dbo.pTemporaryTable AS CREATE TABLE #Temp(ID int, Country varchar(10)) -- create a temporary table -- insert a few records INSERT #Temp VALUES (1, 'US') INSERT #Temp VALUES (2, 'UK') INSERT #Temp VALUES (3, 'Germany') INSERT #Temp VALUES (4, 'France') SELECT * FROM #Temp -- select records DROP TABLE #Temp – drop the temporary table Test the stored procedure in Query Analyzer EXEC dbo.pTemporaryTable Output: ID Country ----------- ---------- 1 US 2 UK 3 Germany 4 France (4 row(s) affected) Now trying to use the stored procedure as Source in a DTS package will bring the error message: Error Source: Microsoft OLE DB Provider for SQL Server Error Description: Invalid object name ‘#Temp’ I hoped this has been fixed in SQL Server 2005 (Beta 3) version, but when I tried to use the stored procedure as a Source I got the error: Error at Data Flow Task [OLE DB Source ]: An OLE DB error has occurred. Error code: 0x80004005 An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80004005 Description: "Invalid object name '#Temp'." Error at Data Flow Task [OLE DB Source ]: Unable to retrieve column information from the data source. Make sure your target table in the database is available. ADDITIONAL INFORMATION: Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap) 2. Table data type sample CREATE PROCEDURE dbo.pTableDataType AS DECLARE @Temp TABLE (ID int, Country varchar(10)) -- create table -- insert a few records INSERT @Temp VALUES (1, 'US') INSERT @Temp VALUES (2, 'UK') INSERT @Temp VALUES (3, 'Germany') INSERT @Temp VALUES (4, 'France') SELECT * FROM @Temp -- select records Test the stored procedure in Query Analyzer EXEC dbo.pTableDataType Output: ID Country ----------- ---------- 1 US 2 UK 3 Germany 4 France (4 row(s) affected) This time the error will not appear when the Source is provided, but when the package is run: Error message: Invalid Pointer In Server 2005 (Beta 3), when I tried to run the package this went bad again: SSIS package "Package6.dtsx" starting. Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning SSIS package "Package6.dtsx" finished: Canceled. In fact I saw there will be problems because in Flat File Connection Manager’s Preview no data were returned. I hope these problems were solved in the last SQL Server Release, if not we are in troubles! Possible Solutions: If the Stored Procedures provided above are run in SQL Query Analyzer or using ADO, they will work without problems. This behavior is frustrating, especially when the logic is really complicated and you put lot of effort in it; however there are two possible solutions, both involving affordable drawbacks: 1. Create a physical table manually or at runtime, save the data in it and later remove the table. This will not work for concurrent use (it works maybe if the name of the table will be time stamped), but for ad-hoc reports might be acceptable. 2. Built an ADO based component which exports the data to a file in the format you want. The component is not difficult to implement, but additional coding might be requested for each destination type. The component can be run from a UI or from a DTS package. 3. In SQL Server Yukon is possible to use the CLR and implement the logic in a managed stored procedure or table valued function. I just hope it works.