Thank you all. I didn't know about it. I will try to use tmptable and see it works and get back here. Spunny

Hi, We use SSIS 2005. I have a dataflow task with OLEDB Source task and Flat File destination task. I am trying to execute stored proc with parameters using OLEDB Source task and connecting it to Flat File Destination using green constraint. In OLEDB Source Editor, I did this: EXEC uspGetData @pTradeDate = ?, @pType = ?, @pClient = ? I have create 3 variables pTradeDate, pType, pClient at global level. I mapped these variables to stored proc parameters. When I try to parse or preview, I get an error: No value given for one or more required parameters. When I googled, user say that we get errors. All variables have been given default values. In the same Editor, when I click on 'Columns' in left hand side editor, I am not seeing columns in 'Available External Columns' table. So, I can't create mapping for flat file destination. Can someone please help in handling this. Thanks, Spunny

Hello Spunny, I think the issue will be fixed by you specifying initial default values for each variable. So namely, pTradeDate, pType, pClient all need to have a valid value assigned to them in the variables window. Arthur My Blog

When you set up the default values for your variables - did you happen to put quotes around them?Chuck

Hi ArthurZ, I have given default values to all my variables in variables window. oldjeep, No. I haven't put quotes around them. 2 of them are string type and 1 is datetime. I have used string variables before in SSIS packages. I never put quotes around any variable values.

Try one more thing - define the stored procedure call by just sending the param values without the param names EXEC uspGetData ?,?, ? Chuck

I guess you wanted a return value from the stored proc be used in the FF, right? Arthur My Blog

I guess you wanted a return value from the stored proc be used in the FF, right? Arthur My Blog If he did then he's missing an OUT in the callChuck

If I do that and click on either paramters button or preview button, I am getting error and columns is not showing any columns in the table

yes. The stored proc returns result set that needs to go into flat file.

Can you provide the header code for your stored procedure? Just the section that defines the parameters.Chuck

Got it, you must publish the metadata to make the 'Columns' issue go away. See here how: http://www.windows-tech.info/15/e7e4a96178d88c7e.phpArthur My Blog

ALTER PROCEDURE [dbo].[uspGetData] @pTradeDate DATETIME, @pType VARCHAR(10), @pClient VARCHAR(25) AS

Where is the 'declare'? Can we see the SP?Arthur My Blog

Hi Arthur, Thanks for the link. But our company blocked this page saying security threat. So, can't take a look at it.

Show us the SP code (omitting any sensitive stuff)Arthur My Blog

OK - I put a little test package together. The error you are getting when previewing seems to be the norm for a parameterized query. The real mystery is why your column list isn't showing up. The full proc code would help to see if there is anything weird in there that is keeping SSIS from figuring out a column list Chuck

possible oldjeep. I have dynamic sql in my stored proc that gives final result set. I have taken out lot of code and made it little simple. Here is my SP. I had to use dynamic sql because I need left or inner join based on type. I get data into tmp table from another system.. But I gave final dynamic select query.. ALTER PROCEDURE [dbo].[uspGetData] @pTradeDate DATETIME, @pType VARCHAR(10), @pClient VARCHAR(25) AS CREATE TABLE #tmpTbl ( PositionID int, TransactionNumber int ) DECLARE @selectQuery NVARCHAR(MAX), @cashQuery NVARCHAR(MAX), @colQuery NVarchar(MAX), @cteQuery NVARCHAR(MAX); SET @cteQuery = ';WITH CashTrans AS ( SELECT T.TransNum, TT.PositionID FROM Trades T INNER JOIN #tmpTbl TT ON TT.TransactionNumber = T.TransNum WHERE T.Type = ''Cash'')' SET @selectQuery = 'SELECT * FROM Trades T ' SET @cashQuery = 'LEFT JOIN #tmpTbl TT ON TT.TransactionNumber = T.transnum' IF (@pType = 'Cash') BEGIN SET @selectQuery = @selectQuery + @cashQuery END ELSE IF (@pType = 'Col') BEGIN SET @colQuery = 'JOIN CashTrans CT ON (CT.TransNum = T.TransNum OR CT.TransNum = T.Masttick)' SET @selectQuery = @cteQuery + @selectQuery + @colQuery + @cashQuery END EXEC sp_executesql @selectQuery

OK - that is the issue. And your proc is not going to work with SSIS unless it returns a consistant set of columns. Even if it does the oledb command is not going to extract a column list from dynamic sql in a proc. If the dynamic query does return a consistant set of columns then you can explicitly create a temp table and select the results of your dynamic sql into it and then select all from your temp table. Chuck

EXEC sp_executesql @selectQuery spoils the intent, you will not be able to get the "Columns" working (retrieved) this way. Since you only branch out once in the code consider creating two SPs, one for "Cash" and another for "else". Arthur My Blog

Thank you all. I didn't know about it. I will try to use tmptable and see it works and get back here. Spunny