List of all dtsx files used in [Integration Services Catalog] - I need help with the SELECT statement

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 25, 2017 at 9:42 am

  #324703

  How may I write a SELECT statement that will list each SSIS Package and the dtsx files that go with it ?
  I need some direction please ...

• Thom A

  SSC Guru

  Points: 99130

  More actions

  July 25, 2017 at 9:47 am

  #1952607

  I'm not unsure what you mean here, as package is a dtsx file. A package isn't a separate item to a file. Also, what is your deployment method?

  For SSISDB:
  SELECT [name]
FROM SSISDB.catalog.packages;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 25, 2017 at 10:03 am

  #1952609

  Thx, That worked. May I ask a related question.
  Is the content of the dtsx file stored in a VARBINARY column in a some table ? ( Similar to RDL files that are stored in the Catalog table in the ReportServer database )

• Thom A

  SSC Guru

  Points: 99130

  More actions

  July 25, 2017 at 10:10 am

  #1952611

  mw112009 - Tuesday, July 25, 2017 10:03 AM

  Thx, That worked. May I ask a related question.
  Is the content of the dtsx file stored in a VARBINARY column in a some table ? ( Similar to RDL files that are stored in the Catalog table in the ReportServer database )

  Yes, they are.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 25, 2017 at 11:14 am

  #1952625

  Thom A - Tuesday, July 25, 2017 10:10 AM

  mw112009 - Tuesday, July 25, 2017 10:03 AM

  Thx, That worked. May I ask a related question.
  Is the content of the dtsx file stored in a VARBINARY column in a some table ? ( Similar to RDL files that are stored in the Catalog table in the ReportServer database )

  Yes, they are.

  What would be the table name and column name ?

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 25, 2017 at 11:52 am

  #1952633

  mw112009 - Tuesday, July 25, 2017 10:03 AM

  Thx, That worked. May I ask a related question.
  Is the content of the dtsx file stored in a VARBINARY column in a some table ? ( Similar to RDL files that are stored in the Catalog table in the ReportServer database )

  Found!

  USE MSDB
  GO
  Select top 100 packagedata, * FROM dbo.sysssispackages;

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 25, 2017 at 12:01 pm

  #1952634

  Can you help... Why an I not seeing proper output when I run the first SQL statement below.

  
--The one below does not works
USE MSDB
GO
Select top 100 packagedata,
(CONVERT(varchar(max),convert(varbinary(max),packagedata))) as dtsx
FROM dbo.sysssispackages;

  --The one below works
USE REPORTSERVER
GO
Select top 100 Content,
(CONVERT(varchar(max),convert(varbinary(max),CONTENT))) as dtsx
FROM dbo.CATALOG;


• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 25, 2017 at 12:08 pm

  #1952636

  mw112009 - Tuesday, July 25, 2017 12:01 PM

  Can you help... Why an I not seeing proper output when I run the first SQL statement below.

  
--The one below does not works
USE MSDB
GO
Select top 100 packagedata,
(CONVERT(varchar(max),convert(varbinary(max),packagedata))) as dtsx
FROM dbo.sysssispackages;

  --The one below works
USE REPORTSERVER
GO
Select top 100 Content,
(CONVERT(varchar(max),convert(varbinary(max),CONTENT))) as dtsx
FROM dbo.CATALOG;


  Please ignore.. I found for some odd reason it only shows a couple of chars...  but I modified the SQL statement and it does have the content..

  The output from the 3rd column displays large numbers so that means it does output the entire content of the dtsx file.
  No need to worry, i can work from here.. No replies needed! Thx.
  
USE MSDB
GO
Select top 100 packagedata,
(CONVERT(varchar(max),convert(varbinary(max),packagedata))) as dtsx,
LEN(CONVERT(varchar(max),convert(varbinary(max),packagedata))) as Lendtsx
FROM dbo.sysssispackages;


• Thom A

  SSC Guru

  Points: 99130

  More actions

  July 25, 2017 at 12:08 pm

  #1952637

  If you're using SSISDB, your packages would t be located in MSDB, they'll be in SSISDB. Which are you using?

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 25, 2017 at 12:40 pm

  #1952646

  Thom A - Tuesday, July 25, 2017 12:08 PM

  If you're using SSISDB, your packages would t be located in MSDB, they'll be in SSISDB. Which are you using?

  OK, I got it wrong here.. What table/col has the dtsx file data ?  I am interested in packages in the SSISDB database

• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 25, 2017 at 12:46 pm

  #1952648

  i seem to remember that the package_data column of the SSISDB.[internal].[packages] is actually an encrypted value stored as a varbinary columns, so you cannot cleanlyconvert it to search the xml contents. you'd have to have the encryption algorithm that is used by integration services...at some point we gave that a master key when we created the SSISDB database, but I'm not sure what the method is, even if i had the password.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Thom A

  SSC Guru

  Points: 99130

  More actions

  July 25, 2017 at 1:01 pm

  #1952658

  I'm with Lowell here. I think this is something I referenced in a previous topic with you.

  I'll have a go at decrypting the XML at the office tomorrow, but I'm not hopeful. Why do you need to unencrypted it though? You have the dtsx files anyway. If you don't, then why are you trying to get them, they are encrypted for a reason .

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 25, 2017 at 1:02 pm

  #1952660

  mw112009 - Tuesday, July 25, 2017 12:40 PM

  Thom A - Tuesday, July 25, 2017 12:08 PM

  If you're using SSISDB, your packages would t be located in MSDB, they'll be in SSISDB. Which are you using?

  OK, I got it wrong here.. What table/col has the dtsx file data ?  I am interested in packages in the SSISDB database

  How come I dont get a single hit on the following query.. I thought this would be the place to find the content that goes in the dtsx file 
  
select * from ssisdb.internal.packages where package_data is not null


• Lowell

  SSC Guru

  Points: 323518

  More actions

  July 25, 2017 at 1:13 pm

  #1952667

   https://docs.microsoft.com/en-us/sql/integration-services/service/ssis-catalog

  ---

  the catalog automatically encrypts the package data and sensitive values

  here's what i did: narrow down what table it could possibly be:
  SELECT
OBJECT_SCHEMA_NAME([colz].object_id),
OBJECT_NAME([colz].object_id),
* FROM sys.columns colz
WHERE TYPE_NAME([colz].[user_type_id]) IN('varbinary','image')
  AND OBJECT_SCHEMA_NAME([colz].object_id) <> 'sys'
  AND [colz].[max_length] = -1

  for me, [internal].[packages].package_data and [internal].[object_versions].[object_data] looked promising;

  thsi clearly showed me one of them was encrypted:
  Select object_data,
(CONVERT(varchar(max),convert(varbinary(max),object_data))) as dtsx,
LEN(CONVERT(varchar(max),convert(varbinary(max),object_data))) as Lendtsx
FROM ssisdb.[internal].[object_versions];

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• mw_sql_developer

  SSCoach

  Points: 19445

  More actions

  July 25, 2017 at 1:23 pm

  #1952670

  Lowell - Tuesday, July 25, 2017 1:13 PM

   https://docs.microsoft.com/en-us/sql/integration-services/service/ssis-catalog

  ---

  the catalog automatically encrypts the package data and sensitive values

  here's what i did: narrow down what table it could possibly be:
  SELECT
OBJECT_SCHEMA_NAME([colz].object_id),
OBJECT_NAME([colz].object_id),
* FROM sys.columns colz
WHERE TYPE_NAME([colz].[user_type_id]) IN('varbinary','image')
  AND OBJECT_SCHEMA_NAME([colz].object_id) <> 'sys'

  for me, [internal].[packages].package_data and [internal].[object_versions].[object_data] looked promising;

  thsi clearly showed me one of them was encrypted:
  Select object_data,
(CONVERT(varchar(max),convert(varbinary(max),object_data))) as dtsx,
LEN(CONVERT(varchar(max),convert(varbinary(max),object_data))) as Lendtsx
FROM ssisdb.[internal].[object_versions];

  We do not have all the dtsx files. So we are trying to get it from the DB. You are right. They are encrypted. So it would be of no use to me at this point.  
  Reason why we need:  We are looking for all objects that reference  a certain column in a certain table. Now, I managed to search views, sps, functions and SSRS reports , but the last item left is   DTSX files. So this is now going to be  a challenge.