SampleDataContract.sql 1.21 KB
Newer Older
dwentzel's avatar
dwentzel committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
USE tempdb
GO
IF OBJECT_ID('dbo.SampleDataContract') IS NOT NULL
    DROP PROCEDURE dbo.SampleDataContract;
GO

	/*
	This code demonstrates how using a "data contract" in your procedure will 
	prevent "metadata" errors when you use temp tables.  

	See also:  
			http://www.davewentzel.com/content/ssis-and-data-contracts

	
	*/


CREATE PROCEDURE dbo.SampleDataContract
AS
BEGIN
	SET NOCOUNT ON

	--uncomment this block to declare a "data contract" that will eliminate 
	--errors with tools that attempt to read the metadata of the output and fail
	--because temp tables do not exist.  Data contracts need to be near the top of the 
	--procedure

	--remove this block to see the error in SSIS
	IF 1 = 0 
	BEGIN
		--declared data contract.  Keep this code block in sync with the actual
		--result set of the procedure
		SELECT 
			CONVERT(BIGINT, 123) AS ID,
			CONVERT(varchar(500),'test') AS Foo,
			CONVERT(varchar(200),'test') AS Bar
	END

	CREATE TABLE #MyTempTable (
		ID BIGINT NOT NULL IDENTITY(1,1) ,
		Foo varchar(500) NOT NULL,
		Bar varchar(200) NOT NULL
	);

	INSERT INTO #MyTempTable
	SELECT name, type_desc
	FROM sys.objects

	SELECT ID,Foo,Bar FROM #MyTempTable;

END
GO