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

Question

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

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

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

Thom A SSC Guru Points: 99127 More actions · Answer 1

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 · Answer 2

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: 246937 More actions · Answer 3

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Thom A SSC Guru Points: 99127 More actions · Answer 4

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