how to return a table from a stored procedure in ssis

Question

i m having a scenario where in i have 2 write a stored procedure which should return me all the records from a table.... along with the no of rows that are present in that table...

now how can i return multiple records...after retriving i have to store the result in some file....

so can anybody let me know how this can be done... i have 2 use ssis......

its urgent plz let me know if u have any ideas.......

---

Priyanka.Sutrave

Answer 1

You could use the stored procedure in a Source of a Data Flow and send the data to a Row Count transform that counts the rows.

What do you plan to DO with the data you get from said Stored Procedure?

---

Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Answer 2

ya for eg : the sp takes both input parameters n output parameters......

the op parameter returns the rowcount....

and that sp should return hole table.....

and once i'll get all the values i have to send those to some file....

---

Priyanka.Sutrave

Answer 3

So my first response is valid. Disregard the output parameter and use a simple Row Count transform in your data flow to save the number of rows extracted to a User variable. Send the data in the pipe line to a flat file Destination.

---

Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Answer 4

how can i give the parameters dynamically to dat sp in oledb src......

---

Priyanka.Sutrave

Answer 5

You could set up your data flow with a Source connection for OLE DB. As your Data access mode, select SQL Command and enter your SQL Statement there, supplying a ? for every parameter. Then in the Parameters section, list a paramter for ecah ? in the statement.

---

Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

Answer 6

Using ?

http://geekswithblogs.net/stun/archive/2009/03/05/mapping-stored-procedure-parameters-in-ssis-ole-db-source-editor.aspx