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

• hoseam

  SSCertifiable

  Points: 5533

  More actions

  October 3, 2017 at 3:57 am

  #402559

  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

• Thom A

  SSC Guru

  Points: 99137

  More actions

  October 3, 2017 at 4:43 am

  #1962022

  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

• hoseam

  SSCertifiable

  Points: 5533

  More actions

  October 3, 2017 at 5:36 am

  #1962024

  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.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  October 3, 2017 at 5:43 am

  #1962026

  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.

  ---

• Thom A

  SSC Guru

  Points: 99137

  More actions

  October 3, 2017 at 7:24 am

  #1962057

  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