SSIS Temp Table error

-2

I have SP contain temp table and generating 4 column in the output as id,name,original and changed the output need to save in flat file.

can anyone please suggest how to do it with Execute SQL Task.

i tried to do with oledb with data access mode as SQL command.but getting error 0x80040E14.

may any one suggest something. Thanks

ssis
asked on Stack Overflow Aug 14, 2015 by Rajee Kasthuri

1 Answer

0

Borrowing the answer from here, you can use WITH RESULT SETS to tell the SSIS engine in advance what data will be returned by the stored procedure.

With this procedure:

CREATE PROCEDURE dbo.UseTempTable
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #MyTable(WhoCares INT NOT NULL IDENTITY(1,1) PRIMARY KEY, Whatever VARCHAR(99));

    INSERT INTO #MyTable(Whatever) VALUES('Minger');
    INSERT INTO #MyTable(Whatever) VALUES('Bonjour');

    SELECT ColInt = WhoCares
        ,  ColVarChar = Whatever
        FROM #MyTable


END;

I would get an error in SSIS 2012 if I tried to use it as a datasource in a dataflow.

Before 2012, there were various tricks to show a "pretend" recordset such as SET FMTONLY ON... SET FMTONLY OFF but they don't work in 2012.

From Phil's article, we just need to specify our Data Flow datasource as

EXEC('EXEC dbo.UseTempTable') 
    WITH RESULT SETS
    ( ( ColInt INT NOT NULL, ColVarChar VARCHAR(99)) )

More information: link

answered on Stack Overflow Aug 14, 2015 by AHiggins • edited Aug 14, 2015 by AHiggins

User contributions licensed under CC BY-SA 3.0