April 25, 2017 at 11:00 am
Is there a way to search a SQL2012 Integration Services instance for SSIS jobs that update a specific table?
Can I complete this query to get what I'm looking for? USE SSISDB
select * from ???
where ??? like '%<table name>%'
April 25, 2017 at 11:25 am
dan-572483 - Tuesday, April 25, 2017 11:00 AMIs there a way to search a SQL2012 Integration Services instance for SSIS jobs that update a specific table?Can I complete this query to get what I'm looking for?
USE SSISDB
select * from ???
where ??? like '%<table name>%'
This can't be done. Packages are held in SSISDB in encrypted format.
An alternative is to use a Windows search tool to search through the package files in your VCS.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 25, 2017 at 2:47 pm
Phil Parkin - Tuesday, April 25, 2017 11:25 AMdan-572483 - Tuesday, April 25, 2017 11:00 AMIs there a way to search a SQL2012 Integration Services instance for SSIS jobs that update a specific table?Can I complete this query to get what I'm looking for?
USE SSISDB
select * from ???
where ??? like '%<table name>%'This can't be done. Packages are held in SSISDB in encrypted format.
An alternative is to use a Windows search tool to search through the package files in your VCS.
Does windows search search in file contents though for DTSX's?
I found I needed to use a tool like astrogrep to search through the DTSX's. But maybe this is better in Windows versions higher than 7 OR I just need to learn how to search better.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 26, 2017 at 5:47 am
bmg002 - Tuesday, April 25, 2017 2:47 PMPhil Parkin - Tuesday, April 25, 2017 11:25 AMThis can't be done. Packages are held in SSISDB in encrypted format.
An alternative is to use a Windows search tool to search through the package files in your VCS.Does windows search search in file contents though for DTSX's?
I found I needed to use a tool like astrogrep to search through the DTSX's. But maybe this is better in Windows versions higher than 7 OR I just need to learn how to search better.
I'm on W10, and from there I see that I could modify my indexing options to include DTSX files.
But I haven't, because I don't do it that way. Instead, I use FileSeek from Binary Fortress. This is fast and allows Regex matching, which can come in very useful for advanced searches.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 26, 2017 at 6:12 am
Brandie did an article which included searching the dtsx files not too long ago using PoSh: http://www.sqlservercentral.com/articles/database+dependencies/153072/?utm_source=SSC&utm_medium=pubemail
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 26, 2017 at 6:49 am
Thom A - Wednesday, April 26, 2017 6:12 AMBrandie did an article which included searching the dtsx files not too long ago using PoSh: http://www.sqlservercentral.com/articles/database+dependencies/153072/?utm_source=SSC&utm_medium=pubemail
That is a good article, but unfortunately it's not useful in this instance because it depends on packages having been deployed to MSDB rather than SSISDB
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 26, 2017 at 7:02 am
Phil Parkin - Wednesday, April 26, 2017 6:49 AMThom A - Wednesday, April 26, 2017 6:12 AMBrandie did an article which included searching the dtsx files not too long ago using PoSh: http://www.sqlservercentral.com/articles/database+dependencies/153072/?utm_source=SSC&utm_medium=pubemailThat is a good article, but unfortunately it's not useful in this instance because it depends on packages having been deployed to MSDB rather than SSISDB
The PoSh should work, for example i just ran some PoSh similar to:Select-String -Path "C:\VSProjects\SSIS Projects\Trunk\Packages\*.dtsx" -Pattern brpolicy | group path | select name
This returned any packages i had, in that project, that reference a table named brpolicy.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 26, 2017 at 10:27 am
Can you do a search like that in TFS?
April 26, 2017 at 11:26 am
dan-572483 - Wednesday, April 26, 2017 10:27 AMCan you do a search like that in TFS?
On a local repo, yes, they're just files.
On the TFS server, no, the files are held in SQL Server tables, so the search would be different.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply