Inserting xml from a dtsx file into table for analysis of SSIS package

• alam.mushir-512938

  SSChasing Mays

  Points: 628

  More actions

  January 20, 2012 at 5:06 am

  #263225

  What would be the best way to get all the relevant information regarding database objects being used inside an SSIS package and insert that into a table? I have got a situation where I have to analyse around 100 SSIS packages and find out the DB objects(database,table,view,procedure,function,column) they are touching. I tried using xml source inside a data flow task but BIDS is not able to generate the XSD, says there are too many namespaces.

  Please Help.

  Thanks in advance.

  MMA

• alam.mushir-512938

  SSChasing Mays

  Points: 628

  More actions

  January 20, 2012 at 5:09 am

  #1436380

  I would like to add that I am not very comfortable with xml data and the xml task in SSIS.

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  January 20, 2012 at 5:29 am

  #1436393

  This might not be an enjoyable task.

  Where are the packages currently stored? If they're in the file system, you might be able to do some sort of wildcard text search within the actual DTSXs.

  ---

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  January 20, 2012 at 5:30 am

  #1436394

  Also, is there any external configuration going on? If so, merely searching the packages is not enough, you'll also need to look in the config files/areas.

  --edit: fix typo

  ---

• Elliott Whitlow

  SSC Guru

  Points: 102402

  More actions

  January 20, 2012 at 10:30 am

  #1436628

  You might look at:

  http://sqlblog.com/blogs/peter_debetta/archive/2006/07/13/Using-XML-Data-Type-Methods-to-query-SSIS-Packages.aspx

  This is where I started from. Word of warning, it aint pretty.. but it seems to work.

  CEWII