oledb source issues "<column Name> cannot be found in datasource"

Good morning,

I have written a package which accepts variables for the server, initial catalog & table name.

I execute sql to drop the following stored procedure, then following sql statement to create it.

================================================================

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[SP_CreateMatchProc]
@sTable varchar(300)
as
BEGIN
SET NOCOUNT ON
declare @cmd nvarchar(2000)

set @cmd = ''''
Set @cmd = 'SELECT REPLACE(field1 + field2 + field3 + field4 + field5, '' '', '''') AS dBString '
+ 'FROM ' + @sTable + ' ORDER BY <table>_ID COLLATE Latin1_General_CI_AS'
exec (@cmd)
END
GO

================================================================

Then in the Oledb source (validateexternalmetadata = false) I use "sqlcommand from variable" with a variable value of "SP_CreateMatchProc '<tableName>'"

The package runs fine in the IDE regardless of variable values, but when I created a batch file which calls dtexec I get a failure:

Error: 2007-10-04 08:46:42.82
Code: 0xC0202005
Source: Data Flow Task OLE DB Source [310]
Description: Column "dBString" cannot be found at the datasource.
End Error
Log:
Name: OnError
Start Time: 2007-10-04 08:46:42
End Time: 2007-10-04 08:46:42
End Log
Log:
Name: OnError
Start Time: 2007-10-04 08:46:42
End Time: 2007-10-04 08:46:42
End Log
Error: 2007-10-04 08:46:42.82
Code: 0xC004701A
Source: Data Flow Task DTS.Pipeline
Description: component "OLE DB Source" (310) failed the pre-execute phase and returned error code 0xC0202005.
End Error

with the ValidateExternalMetadata set to TRUE I get

Error: 2007-10-04 09:21:35.20
Code: 0xC004706B
Source: Data Flow Task DTS.Pipeline
Description: "component "OLE DB Source" (10621)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
End Error

the most notable thing I see there is that it looks like a different ID (310) with out the validation and (10621) with it.

Any help would be greatly appreciated.

thanks for the reply, What that post describes is a bit different from what I am doing. The package doesnt fail on the execute sql task step in control flow, rather on the OLEDB source in the data flow.

The steps are loosely like this:

control flow

1. execute sql task: drop the stored proc

2. execute sql task: create the stored proc to select from a table name supplied from a package level variable.

3. move to the Data Flow (loose steps below)

4. Clean up files and drop stored procedure.

Data flow:

1. Process a flat file

2. use an OLEDB source to call the stored procedure to get a recordset for matching.

3. do more processing and some writes to the dB

Phil Brammer wrote:

Right, so in step 2 of the data flow, make sure you have "SET FMTONLY OFF" listed at the top of the SQL where you execute the stored procedure. And the stored procedure needs to have "SET NOCOUNT ON" at the top of its SQL. Also, you'll need to have a "select * from table" statement at the bottom of the stored procedure to return a resulset.

thank you for the reply

This is the sql text I use in the execute sql task:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[SP_CreateMatchProc]
@sState varchar(300)
as
BEGIN

declare @cmd nvarchar(2000)

set @cmd = ''''
Set @cmd = 'SELECT REPLACE(<field1>+ <field2> + <field3> + <field4> + <field5>, '' '', '''') AS dBString '
+ 'FROM ' + @sState + ' ORDER BY <field1> COLLATE Latin1_General_CI_AS'

exec (@cmd)
END
GO

I am not sure where I need to put the select * syntax. I will read about fmtonly

thanks again

I will add that, thanks again.

it will be later today before I attempt the package again, will post back if it is succesful