My solution to the dreaded "Error at Data Flow Task [OLE DB Source ]: No column information was returned by the SQL command." error.
My solution to the dreaded "Error at Data Flow Task [OLE DB Source ]: No column information was returned by the SQL command." error. The underlining issue occurs when SSIS cannot readily ascertain the expected result set structure. So, we want to hint at that structure very early in the procedure, but we really do not want to issue such a result set so earily on. The SET NOEXEC command is our hero for this senerio, as follows: CREATE PROCEDURE [dbo].[Some_Procedure_Name] AS BEGIN SET NOCOUNT ON; DECLARE @tbl_Var TABLE ( SomeLargeText VARCHAR(MAX) ); /* When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them. */ SET NOEXEC ON; /* We need for SSIS to see this next line near the top of the proc. However, we don't want the (empty) result set. */ SELECT SomeLargeText FROM @tbl_Var /* When SET NOEXEC is OFF, all batches are executed after compilation. */ SET NOEXEC OFF; Alive-n-Watching
November 12th, 2010 4:08pm
Hi there, Thank you for your sharing. Below is another article about this topic for your reference: http://munishbansal.wordpress.com/2009/02/18/set-fmtonly-on-useful-in-tsql-ssis-packages-for-using-temp-tables/ Thanks, Jin ChenJin Chen - MSFT
November 15th, 2010 12:15am
Does this NOEXEC ON .... NOEXEC OFF block reside in code that goes to production? or is this merely a design-time technique to hold SSIS' hand?
December 10th, 2010 5:50pm
The NOEXEC ON .... NOEXEC OFF block does remain in production where it has no collateral effect.Alive-n-Watching
December 10th, 2010 6:05pm