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
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
User contributions licensed under CC BY-SA 3.0