Hi, I'm new to SSIS and try to read data from stored procedure which use exec because of table name variable. For example, my stored procedure looks like below. CREATE PROCEDURE dbo.testSP @sufix nvarchar(10) AS Begin Declare @tableName nvarchar(200) Set @tableName = 'customer_' + @sufix EXEC (' Select * from ' + @tableName ' ) End when I run this stored procedure from ole db source editior using EXEC testSP ? sql command, columns are not generated so cannot do mapping to the destination table. Because tableName is dynamic, i have to use stored procedure like this. Does anyone know a solution for this issue?

Have a look at this: http://consultingblogs.emc.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com

I already looked at this post but doesn't help a lot because i am not using any temp table.

you can create a package variable of type string and write your query there in the expression property and also set EvaluateAsExpression to true and the this variable as source in oledb source http://www.rad.pasfu.com

in your sample script, your expression in the variable should be: "Select * from customer_"+@[User::sufix] be sure that you set the EvaluateAsExpression of this new variable as true.http://www.rad.pasfu.com

I think this is not a good practice because my stored procedure actullay contains more than 100 lines and is run exec ( 100 lines statement ) Also, this stored procedure is used from other application not only SSIS. This means, if we change sp for other application, we need to change the script in SSIS variable as well. Will there be any other possible way ?

just call your stored procedure from your variable expression like: exec mysp ...http://www.rad.pasfu.com