OLE DB Source Error Output

In the Input and Output Properties tab under Advance Editor for OLE DB Source, I cannot remove columns. I copied this Source from a standard template and have made the normal changes to make it work. However I keep getting this error...

Error: 0xC020837B at Load Server Security, OLE DB Source [1]: The output column "DBName" (1632) on the error output has no corresponding output column on the non-error output.

Error: 0xC004706B at Load Server Security, DTS.Pipeline: "component "OLE DB Source" (1)" failed validation and returned validation status "VS_ISBROKEN".

DBName of course is one of the columns that no longer exist, but I can't remove. Whenever I try to remove one of the columns, I get this error...

Error at Load Server Security [OLE DB Source[1]]: The column cannot be deleted. The component does not allow columns to be deleted from this input or output. Is there anything that I can do to remove the columns? Is there just a simple setting that I can change to make this work?
If you just double click on the OLE DB source, you can choose "Columns" on the left hand side and unselect the columns you do not want in the data flow.|||There is a problem with that though because I can't make any changes within the source. I always get the error...

Invalid object name 'tempdb.dbo.Server_Roles. This is because the table isn't created yet. It gets created when the connection manager connects to each server. It is a temporary table. Thus, it won't let me make any changes that I can't make in advance editor.

-Kyle
|||

Why don't you create the table somewhere else, get it configured properly, then change the table name afterward?

|||

That doesn't work. Currently I created a table in the temp directory and pointed the connection manager towards that DB and server. I still can't click on column and I can't change anything unless I hit cancel. My guess is that it may not be connecting to the server and DB correctly. I don't get any explanation for the error, its just one of the H errors.

-Kyle

|||I guess I don't understand what you are trying to do. Please explain further with more detail.

OLE DB Command transform and Output columns.

Hi All,

I have an OLE DB transform with a SQL Command of:

sp_get_sponsor_parent ?,? OUTPUT

where sp_get_sponsor_parent is defined like:

CREATE PROCEDURE [dbo].[sp_get_sponsor_parent]

@.pEID int,

@.results int OUTPUT

AS

BEGIN

.

.

.

END

I map the columns, refresh & OK out of the component without trouble, but on executing the package it fails during validation on this component. I'm utterly stumped.

Any light shed would be greatly appreciated.

Many thanks in advance,

Tamim.

I don't see any 'EXEC' in your sql commnad...may this be the problem?|||

This wasn't the problem Rafael - the EXEC is optional.

I resolved the issue however, by trialing just this one thing - outputting to a (derived) column within OLE DB Command - in a new/clean package. To that end I would like to bring the following example to everyone's attention: it's concise, comprehensive and clear, and thus can be considered canonical. No doubt there are other such examples out there, but this particular one helped me to push forwards, and thus deserves the publicity:

http://wiki.sqlis.com/default.aspx/SQLISWiki/OLEDBCommandTransformationAndIdentityColumns.html?diff=y

Thanks very much for your input Rafael.

Cheers,

Tamim.