Hello, From SSIS, I would like to script out stored procedures that start with the prefix of "RPTACCT_". I was hoping to have all the create procedure statements and their definition all loaded in memory so that I can then execute the script in a SQL Task. Is this possible or is there a better approach? I'm aware that I can do this all by hand, using SQL Server's Generate Scripts... but we have a complicated backup procedure that needs to be automated with the stored procedures. Thanks. -Chris

Idid that using SQL DMO in C#; with SSIS you can take the proper SQL and put into Execute SQL task and dump the resulted into a flat file (SSIS uses in memory [buffers] to capture results). Just in case you need that SQL it is SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME LIKE 'usp%' Arthur My Blog

Hi ArthurZ, I ran your SQL and got the desired list of stored procedures. Do you know the T-sql that scripts out the procedure (Create Procedure SomeStoredProc) if I have a name of it? I think I might have to put all of this in a for each container. Thanks.

Yes, then use SP_helptext Arthur My Blog

Also found this, using SQL DMO, too: http://binaryworld.net/Main/CodeDetail.aspx?CodeId=1143Arthur My Blog

Thanks, ArthurZ. I got it to work!