I have around 500 SSIS package. I wanted to get the list of SSIS package where linked server is used. The reason we have to get this is that we are now removing the linked server. I don't want to open every SSIS package and check all the task to see if link server is available.
Is there any way we can do this?
If you don't want to use powershell, I use something called FnR.EXE which you can google and download. Again you just search through the XML which is just a text file. If you know that names of your linked servers, that's good. If you don't know the names of your linked server you'll have to search for something of the form %.%.%.%. It would be much more reliable to know all the linked server names (it should be quicker to work that out than go through all of your packages)
You also need to consider if your package uses a view which in turn references a linked server. Then the linked server name won't actually appear in the package.