Calling a stored procedure in SSIS 2

[MsMellow]

Help!!!!!

All I want to do is connect to a database, exec a stored procedure that writes to a flat file. Seems so simple and was in DTS. Can someone tell me how to do this is SSIS.

What I have tried:

1. Tried to do it in Wizard, but doesn't seem to allow
execute my_procedure_name in SQL window.

2. Tried to do it from designer by:
a. Creating source connector
b. Moving EXEC SQL Task to designer and
then typing EXEC my_procedure_name
c. Then moving Flat file destination to designer
At this point, can't create flat file destination
because columns are not defined.

I have worked for days on this, what am I doing wrong!!!!

 

[MDXer]

You will need a a Single Data Flow task in the COntrol flow. Within this data task you will have a OLEdb Data Source and a file destination. You will set the Command type in the OLEDB Source to be a SQL COmmand, within this you will place you execute statement. Attach the Source to the destination and configure the destination object.

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[MsMellow]

Hi,

Have been having server problems. You set me in the correct direction, but I found this article on the internet.

http://blogs.conchango.com/jamietho...edures-inside-an-OLE-DB-Source-component.aspx

I tried this, did create the metadata for me but doesn't seem to want to exec my procedure.

Thanks,

MsMellow

 

[mlcollins]

Hi - I was having a similar problem and found this post:

http://www.ssistalk.com/2007/10/10/ssis-stored-procedures-and-the-ole-db-source/

Hope it helps.

-Marianne

 

[MsMellow]

Thank you,

In the Data Flow Task:
SET FMTONLY OFF
Execute my_procedure_name

At top of my procedure:
SET NOCOUNT ON

And it works!!!!!