SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Coping with No Column Names in the SSIS OLEDB Data Source Editor

By Paul Ibison,

Sooner or later you'll create a DataFlow and come across this type of dialog box for the OLE DB source:

Source Connection

No source columns listed and noting to map - great Unahppy face

So, why does this problem occur, when all previous attempts have worked? Well the most likely case is that you are selecting from a temporary table as the last statement in the source stored procedure, and in this case there is no metadata returned to the designer. Just a simple proc like the one below will cause this problem:


SELECT TOP 10 organisation_name INTO #MyOrganisations FROM dbo.Organisation

SELECT * FROM #MyOrganisations

So, what can we do? There are 2 main possibilities (there is also a third one that I'll post up some other time).

(1) One possibility is to change the SQL Command Text as follows and add the italicised text:


exec myproc

This forces the parser to actually run the query to return the metadata and reverse the SET FMTONLY ON that it implicitly uses.

This works, and you'll see the column names as per normal. However there is a serious downside that seems to be missed when this solution is advised. My colleague John Gillies added an auditing step to the proc "myproc" to test this and found that the proc itself was executed 5 times when running the package!!!This could seriously slow things down if the initial stored proc itself is slow anyway. If the proc also does some data modifications, it could be catastrophic.

However, the problems can be offset somewhat and the performance can be optimised by using 2 properties:

I set “DelayValidation” on the Data Flow task to true and the executions of the stored proc went down from 5 to 3 (as the result of a recommendation by Phil Brammer).

Then I set the “ValidateExternalMetadata” setting of the source and dest connections to be false. This further reduced the executions to 2.

Better than 5 but still not down to the 1 execution that I was hoping to achieve.

(2) Use a Table Variable instead


declare @mytable table (organisation_name varchar(100))

INSERT INTO @mytable(organisation_name)
SELECT TOP 10 organisation_name FROM dbo.Organisation

SELECT * FROM @mytable

You'll now need to set the SQL Command Text to be:

exec myproc

Otherwise you'll get the dreaded "A rowset based on the SQL command was not returned by the OLE DB provider" error.

Using this technique on our procedures ensured that the procruns once only! OK - you can't put nonclustered indexes on a table variable and all the other restrictions, so if really needed you can use option (1) but in most cases I'd reason that option (2) is the preferable solution.


Total article views: 6211 | Views in the last 30 days: 4
Related Articles

Temp tables and scope

create table MyTable (id int) go Create Table #Test (id int) go create proc MyProc @id int ...


Linked Server Oracle Unable to execute select QUERY

Cannot execute select query in linked server whereas Insert query works fine


Execute Stored Procedure with Select Statement as Input Parameters

Execute Stored Procedure with Select Statement as Input Parameters


Job failed due to Warning in Source select statement

Job failed due to Warning in Source select statement


Replication Error, The process could not execute 'sp_MSpub_adjust_identity' on 'Servername'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)

Replication Error, The process could not execute 'sp_MSpub_adjust_identity' on 'Servername'. (Source...