Returning SSIS stored procedure name

Hi

I am currently trying to write a number of processe's to keep track of what information is held in my SSIS package. The package I have created is rather large and it would prove a long labourious process to look through every task to see what stored procedure has been used.

What I wanted to do was write a stored procedure in SQL Server 2005 that pick's up each package name and checks for any stored procedures used and returns the names of these stored procedures and any other relevant information i.e required variables.

So far I have managed to create a stored procedure that picks up the name(s) of the packages but I am stuck after this.

Declare @.Filename varchar(1000)
Declare @.cmd varchar(1000)

Create table #dir (Filename varchar(1000))
Insert #dir
Exec master..xp_cmdshell 'dir /B C:\Development\SumColumn\SumColumn\*.dtsx'
delete #dir where Filename is null or Filename like '%not found%'
Select @.Filename = ''
While @.Filename < (select max(Filename) from #dir)

drop table #dir

Any help would be appreciated.

Thanks

Hi,

you can use a "SQL profiler" trace to trace whats happening in your server when the package is run.

anyway here's something that might help you. dtsx file of SSIS is stored in XML format

you can open the file with notepad and figure out from there. you can use the notepad "find" feature

if you have some XML background

or data processing skill you might be able to extract the information you need.

you may also use the Sql server 2005 XML datatype to do that.

regards

,joey

|||

Using the SSIS API, it's generally possible to load a package, find all the instances of some specific tasks, and retrieve the values of some of their properties. At the time that you wrote the code, you'd need to determine which types of tasks you wanted to scan. For each of these types, you'd need to write code to intelligently parse the values stored in the properties of the task for the name of the stored procedure. You can read up on the SSIS API, here: http://msdn2.microsoft.com/en-us/library/ms136025.aspx

In particular, you'll probably want to take a look at:

http://msdn2.microsoft.com/en-us/library/ms136090.aspx

http://msdn2.microsoft.com/en-us/library/ms135956.aspx

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.aspx

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.executables.aspx

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.executables.aspx

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.tasks.executesqltask.executesqltask.aspx

While it might be possible to scan through the xml, that approach would be unsupported.