August 13, 2014 at 2:39 pm
Hi Iam using SQL Server 2012.I have around 120 packages deployed in Integration services catalogs.
I wanted to find the packages that uses a particular tablename. Is there any easy way to perform this from SQL server 2012..?
Please help..!!
August 13, 2014 at 2:56 pm
demin99 (8/13/2014)
Hi Iam using SQL Server 2012.I have around 120 packages deployed in Integration services catalogs.I wanted to find the packages that uses a particular tablename. Is there any easy way to perform this from SQL server 2012..?
Please help..!!
This can be done and it's not too complicated. Unfortunately I don't have my 2012 system at hand but I'll try torture the truth out of my grey cells. From the top of my head you'll have to investigate ssisdb.internal.packages, especially the column package_data. Start with a LIKE query or evern XQuery.
😎
August 13, 2014 at 3:25 pm
Eirikur Eiriksson (8/13/2014)
demin99 (8/13/2014)
Hi Iam using SQL Server 2012.I have around 120 packages deployed in Integration services catalogs.I wanted to find the packages that uses a particular tablename. Is there any easy way to perform this from SQL server 2012..?
Please help..!!
This can be done and it's not too complicated. Unfortunately I don't have my 2012 system at hand but I'll try torture the truth out of my grey cells. From the top of my head you'll have to investigate ssisdb.internal.packages, especially the column package_data. Start with a LIKE query or evern XQuery.
😎
When i look in my internal schema (and the catalog schema for good measure) I find nothing that can be queried for xml nor does any package that is deployed have anything in the package data column.
On the flipside, you can easily see each module within the package.
I am still interested in a means via the ssisdb to view this.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 14, 2014 at 1:28 am
I can't see a way of querying SSISDB directly to get this data. Which makes me wonder where SQL Server has put it!
But for those people who have adopted the project deployment model, your source files are well organised in projects and checked in to source control (I hope). It is easy enough to search across these source files. I use the free version of FileSeek [/url]to do this, which allows me to search across multiple folders using 'simple' or regex matches.
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.
August 14, 2014 at 2:02 am
Phil Parkin (8/14/2014)
I can't see a way of querying SSISDB directly to get this data. Which makes me wonder where SQL Server has put it!
I asked Matt Masson about this some time ago and I believe the contents are encrypted. So you can't query them directly...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 14, 2014 at 7:42 am
Koen Verbeeck (8/14/2014)
Phil Parkin (8/14/2014)
I can't see a way of querying SSISDB directly to get this data. Which makes me wonder where SQL Server has put it!I asked Matt Masson about this some time ago and I believe the contents are encrypted. So you can't query them directly...
Even encrypted, it should show some sort of data in the columns. I get null values - with dozens of packages deployed via project deployment model. I can see plenty of data in the executions and messages tables though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 14, 2014 at 8:40 am
SQLRNNR (8/14/2014)
Koen Verbeeck (8/14/2014)
Phil Parkin (8/14/2014)
I can't see a way of querying SSISDB directly to get this data. Which makes me wonder where SQL Server has put it!I asked Matt Masson about this some time ago and I believe the contents are encrypted. So you can't query them directly...
Even encrypted, it should show some sort of data in the columns. I get null values - with dozens of packages deployed via project deployment model. I can see plenty of data in the executions and messages tables though.
The ispac file is in the object_versions table, encrypted and zipped, the zip is the tricky part.
😎
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply