1

This question already has an answer here:

I have a number of SSIS Packages

I am trying to locate a table that is populated by one of the SSIS Package

is there an easy way to find in the project where database.dbo.tabledata has been used in a script within the project

please help team

marked as duplicate by Kin Shah, Josh Darnell, mustaccio, McNets, Michael Green Feb 8 at 4:17

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • Can you run the package in DEV? if so, then start a Profiler Trace and run the package. It should have an INSERT INTO even you can syphon. The package also has metadata you should be able to search through. – MguerraTorres Feb 4 at 16:28
  • Above article may be helpful to you. dba.stackexchange.com/questions/211796/… – Pratik Patel Feb 5 at 5:34
  • If you cannot execute them as suggested by @MguerraTorres, I would add each DTSX file into a single Visual Studio Solution and perform a Find All on the table name. Follow each hit in the results window until you see the part of the package inserting data. – Steve Strelzyk Feb 5 at 15:59
1

A low rent approach would be to open a command window (Start -> Run => cmd) and navigate to the folder with all the SSIS packages. cd \my\ssis\folder\

The find command looks for a specific string (case sensitive by default).

C:\my\ssis\folder > find "myCaseSensitiveTableName" *.dtsx

or

C:\my\ssis\folder > find /i "myCaseInsensitiveTableName" *.dtsx

That should be a good starting point for looking through your packages and hopefully the table name(s) you're looking for do not collide with the internal xml of an SSIS package. If that's the case, and you can assume OLE DB Destinations, a find for OpenRowset should be beneficial.

0

I guess you are using VS (as your tags say) for developing/editing SSIS. Right click on the .dtsx file and "View Code". Then do a string search. If you have multiple dtsx to look for then CMD is your best friend (mentioned by @billinkc) or alternatively you can use notepad++. In Linux I believe there is a command line function call "grep" (https://www.cyberciti.biz/faq/howto-use-grep-command-in-linux-unix/).

0

There are two methods I'd use personally. The best method probably, is the CTRL + SHIFT + F function. You can search for items like tables at the project level or the solution level. However if you have too many different solutions, you can use the below method.

Another method I've used is powershell, here's the code to find and output the results to a text file.

# Variables you should alter to your environment
$DirectoryToTarget="C:\Git\SSIS\PackageSets1\"
$WordToFind="TableSourceOne"
$PSGrepLog="C:\LogOutputs\PSGrepLog.txt"

# You will probably leave this variable alone. Set to search SSIS. 
$FilterType="*.dtsx"

Clear-Content $PSGrepLog

Get-ChildItem -Path $DirectoryToTarget -Filter $FilterType -Recurse | where { !$_.PSIsContainer } | % { 
    $file = Get-Content $_.FullName
    $containsWord = $file | %{$_ -match $WordToFind}
    If($containsWord -contains $true)
    {
        Add-Content $PSGrepLog $_.FullName
    }
}

Source: Where did I put that thing in my SSIS project?

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