Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to convert the data from stored procedure to excel sheet through SSIS. Expand / Collapse
Author
Message
Posted Wednesday, April 24, 2013 8:55 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:02 PM
Points: 19, Visits: 38
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.
Post #1446275
Posted Thursday, April 25, 2013 1:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 5,078, Visits: 11,856
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1446318
Posted Friday, April 26, 2013 2:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:36 PM
Points: 1, Visits: 104
Even i didn't understand your question, can you explain it clearly?

Harini
Post #1447169
Posted Friday, April 26, 2013 11:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:02 PM
Points: 19, Visits: 38
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.
Post #1447222
Posted Monday, April 29, 2013 8:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:35 AM
Points: 2,819, Visits: 2,563
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.
Post #1447557
Posted Monday, April 29, 2013 8:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 5,078, Visits: 11,856
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1447562
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse