I have a package in which i need to use stored procedure to generate a ID which expects Input parameters and based on the input parameter the procedure will work and generate Id and it will return the value. how I need to pass the value to the stored procedure from ssis package and how to get the returned value from stored procedure to the ssis package.In ssis package package I need the returned vlaue of stored procedure. help me on this fuzailrashid

Use the Execute SQL Task. This should help: The Execute SQL Task From the top of that article: After looking at these introductory topics we will then get into some examples. The examples will show different types of usage for the task: Returning a single value from a SQL query with two input parameters. Returning a rowset from a SQL query. Executing a stored procedure and retrieveing a rowset, a return value, an output parameter value and passing in an input parameter. Passing in the SQL Statement from a variable. Passing in the SQL Statement from a file. I've highlighted the parts that should be interesting to you. @Jamiethttp://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Use and Exectue SQL Task. Set the Result Set property to Single Row. Put in your SQL Command as "SELECT ... FROM ... WHERE <something> = ? AND <something else> = ? ..." On the Parameter mapping tab, map the ? inputs from the command to variables named 0 through 1, with appropriate data types. Then, assuming your stored procedure ends with code such as 'SELECT ID AS Value' set up on the Results page a single result mapped to the SSIS variable you want to hold the returned ID. This should be named 0. If you have two columns returned, name them 0 and 1, etc. Hope this helps.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Hi sandeep I have all the checkins inside the data flow task.If the input is not matching with the target table then i need to perform this step. i cant use execute sql task inside a data flow taskfuzailrashid

Hi sandeep I have all the checkins inside the data flow task.If the input is not matching with the target table then i need to perform this step. i cant use execute sql task inside a data flow task fuzailrashid You never mentioned anything about a dataflow in your first post in this thread. If you want to do something conditionally inside a dataflow using a value returned from a stored procedure then you will need to get the value out of the stored procedure BEFORE executing the dataflow - hence use the Execute SQL Task. If you need to get a value returned from the stored procedure for every row in your dataflow then use the OLE DB COmmand component. Regards Jamiehttp://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Hi sandeep, from the source to target mapping,I need to check whether the id is present in the destination table.If the id is there in the destiantion then I need to update the record in the destination atble. else I need to create a new record in the destination table.I am doing this task inside the dataflow task. when inserting a new record ,there is a column in the detsination table which is secondaryId which needs to be generated by using stored procedure. this stored procedure expects input parameters.i need to pass the input parameters to execute the stored procedure . the secondaryId column is an identity column which needs to be incremented by 1 from the previous value of secondaryId in that table. (for this purpose only I am using stored procedure but i am not getting the actual result) (Is there anyway to check what is the prevoius value of secondaryId in that table and then getting the value and incrementing to a new value,so that I can use this value as secondaryId when inserting new record. And the other thing is, secondaryId is maintained in a separate table where the new value of secondaryId will be maintained as the current identity in that table.) fuzailrashid

You will have to load the data into an intermediate table carry out the required transforms on this table before loading the data into to the final target table Regards, Sandeep