5

I have a number of SSIS packages, how can I search them to find the use of specific tables? I need to make tables changes and I am trying to assess the impact of these.

5

SSIS code behind is actually a XML value and all components of package will be represented as XML node values within it.This XML can be parsed to identify if it has a reference for our searched table or column.

Finding SSIS Packages having References to a Table or Column will help you.

  • You can also use a text crawler or a Powershell Script to comb through your source control directory. – Zane Jul 9 '15 at 18:39
  • 1
    this will not give you everything, if variables are being used you will miss things as you cannot say with 100% certainty what is being used – adolf garlic Sep 28 '16 at 13:19
2

I was looking for a similar solution, to find references of a table in all the SSIS packages I have.

The solution that worked for me was simple, I copied all the SSIS packages that needed to be searched to a folder and used the 'Find in Files' function from notepad++ wherein you can provide the search string and the path to this folder.

It works on the basis that dtsx can be edited as xml on notepad++. I am sure there are other solutions. Hope it helps.

  • For me, too: I used Notepad++. People sometimes forget that SSIS packages are XML, and Notepad++ makes XML easy to understand. Of course that means that the SSIS package must be a windows file, rather than stored in msdb (package deployment model) or SSISDB (project deployment model). In my case, I didn't know the full filename, but knew enough that the "find" feature gave me the tablenames within nodes (for example, table references within source or destination components within dataflow tasks), and the tablename references within SQL scripts (within Execute SQL tasks). – Doug_Ivison Aug 23 at 12:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.