Hi, has anyone experience problems with retriving wrong metadata info from
store procedure?

I have a stored procedure (SP) that joins three tables, I used SSIS OLD DB
Source calling the SP, but the returned data type mapping is wrong and I
can't seem to overwrite them with the correct on, they get resetted. The
place I changed was in the Advanced Editor for OLE DB Source under the "Input
and Output Properties" tab, there's OLE DB Source Output - External Columns,
one of the field should be a four-byte unsigned integer, but it keeps
resetting back to string[DT_STR].

I'm using SS2005 without any service packs installed. Will SP1 & the
cumulative SP resolve this issue?

Thanks,
Benjamin

Hello Benjamin,


Very difficult to say whether the SP will fix. Any chance we can see the
SP and the underlying structures for a repro?

Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

Yes. Here's the stored procedure:

ALTER PROCEDURE [dbo].[GetReportInfoBySourceFilename]
@sourceFileNM varchar(30)
AS

IF EXISTS(SELECT * FROM dim_sourceFile WHERE
dim_sourceFile.sourceFileNM=@sourceFileNM AND reportID=35)
BEGIN
SELECT
'' AS campusID,
'' AS campusNM,
dim_sourceFile.reportID,
dim_reportName.reportNM,
dim_report.frequencyNM
FROM dim_sourceFile
INNER JOIN dim_report ON dim_report.reportID = dim_sourceFile.reportID
INNER JOIN dim_reportName ON dim_reportName.reportID =
dim_sourceFile.reportID
WHERE dim_sourceFile.sourceFileNM=@sourceFileNM
END
ELSE
BEGIN

SELECT
dim_sourceFile.campusID,
dim_campus.campusNM,
dim_sourceFile.reportID,
dim_reportName.reportNM,
dim_report.frequencyNM
FROM dim_sourceFile
INNER JOIN dim_report ON dim_report.reportID = dim_sourceFile.reportID
INNER JOIN dim_campus ON dim_campus.campusID = dim_sourceFile.campusID
INNER JOIN dim_reportName ON dim_reportName.reportID =
dim_sourceFile.reportID
WHERE dim_sourceFile.sourceFileNM=@sourceFileNM

END


The data type of the fields on table:
dim_sourceFile.campusID - char(2)
dim_campus.campusNM - varchar(50)
dim_sourceFile.reportID - int
dim_reportName.reportNM - varchar(255)
dim_report.frequencyNM - varchar(15)

But, in the Advanced Editor for OLE DB Source, it shows the external columns
as:
campusID = string[DT_STR] length 1 (Wrong, should be varchar(2))
campusNM = string[DT_STR] length 1 (Wrong, should be varchar(50))
reportID = four-byte signed integrer [DT_I4]
reportNM = string [DT_STR] 255
frequencyNM = string [DT_STR] 15


"Allan Mitchell" wrote:

Hello Benjamin,


OK so the two it "Incorrectly reads are the ones you say

'' as X

I can see the values you see in the columns being put there when the first
statement is the one being evaluated. What happens if you switch the statements?


Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com

Ah! That seems to work. Use cast works too:

cast('' as char(2)) AS campusID,
cast('' as varchar(50)) AS campusNM,

Thanks!
Benjamin

"Allan Mitchell" wrote:

Hello Benjamin,


Glad my suggestion worked and yep your suggestion here was my next suggestion.
great news

Regards

Allan Mitchell
Konesans Ltd
T +44 7966 476 572
F +44 2071 008 479
http://www.konesans.com