Ssis Cannot Pull Columns From Stored Procedure?

hi,

i have the following stored proc which returns a resultset at the end, i have an SSIS package that calls this stored proc and outputs the result to a file. However, the package fails because it cannot pull the columns for the schema because of the return lines in the middle of the stored proc. If i remove the it works fine, pulls hte columns as normal. but if i leave them in ssis cannot get the columns. what can i do to get around this?



ALTER PROCEDURE [dbo].[uspCreateBrightPointFile]

AS

SET nocount ON

IF EXISTS (SELECT TOP 1 *

FROM brightpointfile)

TRUNCATE TABLE brightpointfile

-- Get the order information from the database where vendorconfirmationID = 0

INSERT INTO brightpointfile

SELECT o.orderid,

o.requestid,

'295193' AS araccountnumber,

o.orderdate,

'PRIORITY' AS shipmethod,

'Asurion Dobson' AS billname,

'PO Box 110808' AS billaddress1,

'Attn Account Receivable' AS billaddress2,

' ' AS billaddress3,

'Nashville' AS billcity,

'TN' AS billstate,

'37222' AS billzip,

c.fullname AS shipname,

Replicate(' ',100) AS shipaddress1,

Replicate(' ',100) AS shipaddress2,

' ' AS shipaddress3,

Replicate(' ',40) AS shipcity,

' ' AS shipstate,

Replicate(' ',10) AS shipzip,

'1' AS linenumber,

ve.sku AS itemcode,

o.quantity AS qty,

r.typeid,

r.customerid,

0 AS addressfound

FROM [order] o WITH (NoLock)

JOIN request r WITH (NoLock)

ON o.requestid = r.requestid

JOIN customer c WITH (NoLock)

ON r.customerid = c.customerid

JOIN (SELECT [subequipid],

[subid],

[clientequipid],

[serialno],

[statusid],

[startdate],

[enddate],

[createdate],

[createuserid]

FROM subequip s1 WITH (NoLock)

WHERE s1.statusid = 1

AND s1.startdate = (SELECT MAX(s2.startdate)

FROM subequip s2 WITH (NoLock)

WHERE s2.statusid = 1

AND s2.subid = s1.subid)) se

ON r.subid = se.subid

JOIN vendorequip ve WITH (NoLock)

ON se.clientequipid = ve.clientequipid

JOIN clientequip ce WITH (NoLock)

ON ce.clientequipid = se.clientequipid

WHERE vendorconfirmationid IS NULL -- order was never sent to CellStar

AND ve.typeid IN (1) -- only pull direct fulfillment (not store fulfillment)

AND ve.vendorid IN (2) -- only pull vendor = CellStar Insurance

AND o.orderdate > '2007-08-01' -- only pull orders after 08/01/2007

--IF @@ERROR <> 0

--RETURN 1

--First use the address types of 2

UPDATE brightpointfile WITH (ROWLOCK)

SET shipaddress1 = b.address,

shipaddress2 = b.address2,

shipcity = b.city,

shipstate = b.stateid,

shipzip = b.zipcode,

addressfound = 1

FROM customeraddress a WITH (NOLOCK),

address b WITH (NOLOCK)

WHERE a.customerid = brightpointfile.customerid

AND b.addressid = a.addressid

AND a.typeid = 2

--IF @@ERROR <> 0

--RETURN 2

--Update the rest where the address type is 1 and there is no type 2

UPDATE brightpointfile WITH (ROWLOCK)

SET shipaddress1 = b.address,

shipaddress2 = b.address2,

shipcity = b.city,

shipstate = b.stateid,

shipzip = b.zipcode

FROM customeraddress a WITH (NOLOCK),

address b WITH (NOLOCK)

WHERE a.customerid = brightpointfile.customerid

AND b.addressid = a.addressid

AND a.typeid = 1

AND brightpointfile.addressfound = 0

--IF @@ERROR <> 0

--RETURN 3

--Select all the records from the temp table, plus union in 2 extra records required by the client

SELECT *

FROM (SELECT orderid,

requestid,

araccountnumber,

orderdate,

shipmethod,

billname,

billaddress1,

billaddress2,

billaddress3,

billcity,

billstate,

billzip,

shipname,

shipaddress1,

shipaddress2,

shipaddress3,

shipcity,

shipstate,

shipzip,

1 AS linenumber,

itemcode,

qty,

0 AS additional

FROM Brightpointfile WITH (nolock)

UNION ALL

SELECT orderid,

requestid,

araccountnumber,

orderdate,

shipmethod,

billname,

billaddress1,

billaddress2,

billaddress3,

billcity,

billstate,

billzip,

shipname,

shipaddress1,

shipaddress2,

shipaddress3,

shipcity,

shipstate,

shipzip,

brightpointdefaultsku.linenumber,

brightpointdefaultsku.sku,

1,

1 AS additional

FROM Brightpointdefaultsku WITH (nolock),

Brightpointfile WITH (nolock)) a

ORDER BY orderid,

additional

IF @@ERROR <> 0

RETURN 1

RETURN 0