locked
how to return a table from a stored procedure in ssis RRS feed

  • 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
    Wednesday, July 14, 2010 12:19 PM

Answers

  • 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.
    Wednesday, July 14, 2010 12:33 PM
  • 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.
    Wednesday, July 14, 2010 12:59 PM
  • 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.
    Friday, July 16, 2010 12:35 PM

All replies

  • 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.
    Wednesday, July 14, 2010 12:33 PM
  • 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
    Wednesday, July 14, 2010 12:56 PM
  • 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.
    Wednesday, July 14, 2010 12:59 PM
  • how can i give the parameters dynamically to dat sp in oledb src......


    Priyanka.Sutrave
    Friday, July 16, 2010 8:54 AM
  • 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.
    Friday, July 16, 2010 12:35 PM