10bit 10bit - 3 months ago 8
SQL Question

Find all Packages that uses Flat Files

One of my coworkers was wondering if there is a way to get a list of all SSIS packages names on Integration services of all packages that uses FlatFiles. There are currently a lot of packages on the server and it will take a very long time to find all packages manually. We are using SQL 2008 r2.

Thank you

Answer
  1. Export all packages from SSIS package store to files.
  2. Search in .dtsx files for <DTS:Property DTS:Name="CreationName">FLATFILE</DTS:Property>. This is an integral part of any Flat File Connection Manager definition.

Note - this applies to 2005 and 2008 packages only!