07 November 2013

Search SSIS packages for table/column references

A lot of companies now use TFS or some other system and keep all their packages in a single project. This means that a copy of all the packages will end up on your local disk.

There is major failing with SSIS that it is sometimes quite difficult to find what a package is actually doing, what it accesses and what it affects.

This is a simple dos script which will search through all packages in a folder for a string and write the names of found packages to an output file.

Just copy the text to a .bat file (I use aaSearch.bat) in the folder with all the package scripts Change the output filename (twice), change the find string value and run it in a dos window. It works on any text file type so you can also search store procedure scripts – but there are easier ways of doing that.

echo. > aaSearch_factSales.txt for /f “delims=” %%a in (‘dir /B /s *.dtsx’) do call :subr “%%a” goto:EOF

:subr findstr “factSales” %1 if %ERRORLEVEL% NEQ 1 echo %1 >> aaSearch_factSales.txt goto:EOF

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 6020 times – thanks for reading.

  • Rate
    [Total: 0    Average: 0/5]
  • Share

Pop spent his formative years working in assembler on IBM Series/1 but retrained in VB when that went out of fashion. He soon realised how little relational database expertise existed in most companies and so started to spend most of his time working on that. He now sticks to architecture, release control / IT processes, SQL Server, DTS, SSIS, and access methods in VB/ASP/.NET/Crystal Reports/reporting services. He has been involved with SQL Server since the old days of v4.2 to v2005. He tries to stay away from anything presentation oriented (see www.mindsdoor.net). Theoretically he is semi-retired but seems to keep being offered potentially interesting work.

View all articles by Pop Rivett