How to find TSQL Code in SSIS Packages   

I've got > 300 very complicated SSIS packages which execute TSQL and stored 
procedures.  SSIS Packages are executed by jobs and some are executed 
manually.  At various times I have issues with executing spids and need to 
hunt down offending code.  I can get to the TSQL command via DMV's but how do 
I find which SSIS Package(s) contains the offending code?  What if I have a 
stored procedure that, for whatever reason, I have to locate all references, 
i.e. sp_send_dbmail.  Is there a better way than opening up 300+ SSIS 
packages and going through them with a fine tooth comb?  Whew !  What a 
lotta' work.  Is there somewhere I can just query the syscomments table for 
SSIS Packages ???  Thanks.  -CqlBoy

date: Thu, 7 Aug 2008 15:48:08 -0700   author:   Cqlboy

Re: How to find TSQL Code in SSIS Packages   

Hi,

Cqlboy wrote:
> I've got > 300 very complicated SSIS packages which execute TSQL and stored 
> procedures.  SSIS Packages are executed by jobs and some are executed 
> manually.  At various times I have issues with executing spids and need to 
> hunt down offending code.  I can get to the TSQL command via DMV's but how do 
> I find which SSIS Package(s) contains the offending code?  What if I have a 
> stored procedure that, for whatever reason, I have to locate all references, 
> i.e. sp_send_dbmail.  Is there a better way than opening up 300+ SSIS 
> packages and going through them with a fine tooth comb?  Whew !  What a 
> lotta' work.  Is there somewhere I can just query the syscomments table for 
> SSIS Packages ???  Thanks.  -CqlBoy

dtsx-files are xml-files. So you can use any tool which supports
searching for text in several files, e.g. grep, find or a good
text editor, e.g. notepad++

hth,
Gerald

date: Fri, 08 Aug 2008 08:49:01 +0200   author:   Gerald Aichholzer

Re: How to find TSQL Code in SSIS Packages   

Thanks for the response.  The problem is I"m minimizing my developers going 
to the desktop so we've elected to store our packages inside the msdb - 
simplifies many things greatly.  I've searched system tables and can't yet 
find any table storing SSIS info I can query that will find targeted TSQL 
code.  It's this same problem I ran into with DTS packages back on SQL 2000, 
which really turned me off.  Security, Optimization, and locating bad or 
forbidden TSQL written by an army of developers was a complete nightmare.  
Any suggestions would be greatly appreciated.  -CqlBoy

"Gerald Aichholzer" wrote:

> Hi,
> 
> Cqlboy wrote:
> > I've got > 300 very complicated SSIS packages which execute TSQL and stored 
> > procedures.  SSIS Packages are executed by jobs and some are executed 
> > manually.  At various times I have issues with executing spids and need to 
> > hunt down offending code.  I can get to the TSQL command via DMV's but how do 
> > I find which SSIS Package(s) contains the offending code?  What if I have a 
> > stored procedure that, for whatever reason, I have to locate all references, 
> > i.e. sp_send_dbmail.  Is there a better way than opening up 300+ SSIS 
> > packages and going through them with a fine tooth comb?  Whew !  What a 
> > lotta' work.  Is there somewhere I can just query the syscomments table for 
> > SSIS Packages ???  Thanks.  -CqlBoy
> 
> dtsx-files are xml-files. So you can use any tool which supports
> searching for text in several files, e.g. grep, find or a good
> text editor, e.g. notepad++
> 
> hth,
> Gerald
>

date: Fri, 8 Aug 2008 17:46:01 -0700   author:   Cqlboy