Creating a SSIS package which will call the stored procedure and output results into a tble

  • Hi,

    I think I understand the logic but but strugling to implement it, I created a package and created user variable called

    Output

    , I'm still testing so names will change.

    Then the Exec SQL Task to call a procedure, I have set the ResultSet to Full Result Set, because I'm expecting 120k records back from the procedure, I need to store those into a table.

    But from here I'm thinking that I should now use a Data Flow task, to insert the results from the proc into a table, now this is where I struggle. can you help as to how do I conclude this task

  • Using a Dataflow task would be a much better idea, however, is the data going to be on the same server anyway? If so, you would be able to do this just in a T-SQL task, using an INSERT statement.

    What is your need to do this via SSIS?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A - Tuesday, October 3, 2017 4:43 AM

    Using a Dataflow task would be a much better idea, however, is the data going to be on the same server anyway? If so, you would be able to do this just in a T-SQL task, using an INSERT statement.

    What is your need to do this via SSIS?

    It's a SSIS Project, and they are on the different server. a proc from one server has to populate a table in anoter server.

  • hoseam - Tuesday, October 3, 2017 5:36 AM

    Thom A - Tuesday, October 3, 2017 4:43 AM

    Using a Dataflow task would be a much better idea, however, is the data going to be on the same server anyway? If so, you would be able to do this just in a T-SQL task, using an INSERT statement.

    What is your need to do this via SSIS?

    It's a SSIS Project, and they are on the different server. a proc from one server has to populate a table in anoter server.

    I agree with Thom ... use the stored proc as a data source in a data flow.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • hoseam - Tuesday, October 3, 2017 3:57 AM

    But from here I'm thinking that I should now use a Data Flow task, to insert the results from the proc into a table, now this is where I struggle. can you help as to how do I conclude this task

    So, where is it you're struggling? If you don't need any kind of transformations then using a Data flow task to copy data from one table to an identical table on another server is quite simple, with just a OLE DB Source and Destination. What have you tried, and what (if any) errors are you getting?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply