Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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:

CREATE PROC myproc AS

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:

SET FMTONLY OFF
SET NOCOUNT ON

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

CREATE PROC myproc AS

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:

SET NOCOUNT ON
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.

http://ssisblog.replicationanswers.com/

Total article views: 5692 | Views in the last 30 days: 32
 
Related Articles
BLOG

Temp tables and scope

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

FORUM

Linked Server Oracle Unable to execute select QUERY

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

FORUM

Execute Stored Procedure with Select Statement as Input Parameters

Execute Stored Procedure with Select Statement as Input Parameters

FORUM

Job failed due to Warning in Source select statement

Job failed due to Warning in Source select statement

FORUM

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...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones