How to convert the data from stored procedure to excel sheet through SSIS.

Question

How to convert the data from stored procedure to excel sheet through SSIS.

Venal

SSC-Addicted

Points: 463
More actions
April 24, 2013 at 8:55 pm

#293026

I have one task, The task having stored procedure and
the task will be run daily and insert the data into Excel sheet.
Regards,
Subash.

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

subbareddyap (4/24/2013)
I have one task, The task having stored procedure and
the task will be run daily and insert the data into Excel sheet.
Regards,
Subash.

What is your question?

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

harinivelmala SSC Rookie Points: 45 More actions · Answer 2

Even i didn't understand your question, can you explain it clearly?

Harini

Venal SSC-Addicted Points: 463 More actions · Answer 3

Hi Harini,

I have a stored procedure, that procedure will be run daily and provide the result data into excel.

So here i need put SSIS package for this task.

The question is "How to convert the data from stored procedure to excel. What are the transformations i need to used in this task?"

Warm Regards,

Subbu.

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 4

I suggest you read these 2 links

http://consultingblogs.emc.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/e8645006-899a-40bb-b144-6d65ee0a6290/

So the real challenge is getting the data out of the stored procedure. You could build a work table and then

INSERT INTO dbo.WorkTable

EXEC dbo.StoredProcedure

Then you can SELECT columns FROM dbo.WorkTable to feed your data into an SSIS data flow and then flow that down to an Excel destination.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Daniel Bowlin (4/29/2013)
I suggest you read these 2 links
http://consultingblogs.emc.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/e8645006-899a-40bb-b144-6d65ee0a6290/
So the real challenge is getting the data out of the stored procedure. You could build a work table and then
INSERT INTO dbo.WorkTable
EXEC dbo.StoredProcedure
Then you can SELECT columns FROM dbo.WorkTable to feed your data into an SSIS data flow and then flow that down to an Excel destination.

A data flow can quite happily consume the results of a stored proc as an OLE DB source without the need for a work table.

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