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

Question

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

Viewing 15 posts - 16 through 30 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

this linky shows how you can peel the orange with a chainsaw:
instead of simply downloading all the projects and using a text editor to search the *.dtsx files, this streams the binary download and sticks it into some tables.

lot of work, in my case i can simply search my local tfs folder for strings anyway, so i'm not even going to try:

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!

Lowell SSC Guru Points: 323518 More actions · Answer 2

mw112009 - Tuesday, July 25, 2017 1:23 PM
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.

Not having the originals is no big deal. you are allowed to download them from the server
Visual Studio>>New Project>>Integration Services Import Project Wizard>>Browse to server/SSISDB catalog.
download and repeat for every project.

there are also powershell scripts that download the whole catalog as well.

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 · Answer 3

Lowell - Tuesday, July 25, 2017 1:27 PM
mw112009 - Tuesday, July 25, 2017 1:23 PM
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.
Not having the originals is no big deal. you are allowed to download them from the server
Visual Studio>>New Project>>Integration Services Import Project Wizard>>Browse to server/SSISDB catalog.
download and repeat for every project.
there are also powershell scripts that download the whole catalog as well.

Lowell: Thx, That worked, however we have many SSIS packages, I guess at this point I will have to do one by one manually. Hmm. In the mean time I found a C# code on accessing SSIS packages. Let me play with it and see whether I can get around this. If not this is the only way out.
Never mind, at least we have a workaround. Thx for the help.

Lowell SSC Guru Points: 323518 More actions · Answer 4

use powershell, it will be easier;
i googled "powershell export ssis package from integration services catalog" and the first four script hits all look familiar enough to work for you.

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!

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 5

I agree with Lowell here. I would export all of the dtsx's using powershell. You should also get all the DTSX's (or ISPAC's) off of your server so you have a secondary location of them. What is your DR plan in the event your SSIS server dies on you?
I'd get some version control system (CVS, SVN, GIT, TFS, etc) and put your DTSX's in there.

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.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 6

bmg002 - Tuesday, July 25, 2017 2:28 PM
I agree with Lowell here. I would export all of the dtsx's using powershell. You should also get all the DTSX's (or ISPAC's) off of your server so you have a secondary location of them. What is your DR plan in the event your SSIS server dies on you?
I'd get some version control system (CVS, SVN, GIT, TFS, etc) and put your DTSX's in there.

Cool! Can you send me a powershell script. I have never used powershell.

We do have backups, No problem! However in the past people have deployed packages ( left the compnay ) , we can not find the original source code.
In the meat time I found some C# code that i am playing with.

Lowell SSC Guru Points: 323518 More actions · Answer 7

This script from Microsoft Script Center worked flawlessly for me:
https://gallery.technet.microsoft.com/scriptcenter/ImportExport-projects-bca5f29f

I had to change the connection string from localhost to my real servername. I could potentially change the target directory, but c:\SSIS was fine for testing.

That script downloads all the items as *.ispac files(compressed zip, just renamed), in the same relative folders! so you potentially download hundreds of projects in one swoop.

Then in visual studio, for anything I relaly need to open in order to modify,I do the same logic as before, but with a slight variation on the target:
Visual Studio>>New Project>>Integration Services Import Project Wizard>>Radio button for Project Deployment File>>Browse to teh C:\SSIS folder + the *.ispac of your choice.

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 · Answer 8

Lowell - Wednesday, July 26, 2017 6:04 AM
This script from Microsoft Script Center worked flawlessly for me:
https://gallery.technet.microsoft.com/scriptcenter/ImportExport-projects-bca5f29f
I had to change the connection string from localhost to my real servername. I could potentially change the target directory, but c:\SSIS was fine for testing.
That script downloads all the items as *.ispac files(compressed zip, just renamed), in the same relative folders! so you potentially download hundreds of projects in one swoop.
Then in visual studio, for anything I relaly need to open in order to modify,I do the same logic as before, but with a slight variation on the target:
Visual Studio>>New Project>>Integration Services Import Project Wizard>>Radio button for Project Deployment File>>Browse to teh C:\SSIS folder + the *.ispac of your choice.

Cool! I have never run a power shell script before. Is this something you run at the command prompt in windows ?

mw_sql_developer SSCoach Points: 19445 More actions · Answer 9

mw112009 - Wednesday, July 26, 2017 6:58 AM
Lowell - Wednesday, July 26, 2017 6:04 AM
This script from Microsoft Script Center worked flawlessly for me:
https://gallery.technet.microsoft.com/scriptcenter/ImportExport-projects-bca5f29f
I had to change the connection string from localhost to my real servername. I could potentially change the target directory, but c:\SSIS was fine for testing.
That script downloads all the items as *.ispac files(compressed zip, just renamed), in the same relative folders! so you potentially download hundreds of projects in one swoop.
Then in visual studio, for anything I relaly need to open in order to modify,I do the same logic as before, but with a slight variation on the target:
Visual Studio>>New Project>>Integration Services Import Project Wizard>>Radio button for Project Deployment File>>Browse to teh C:\SSIS folder + the *.ispac of your choice.
Cool! I have never run a power shell script before. Is this something you run at the command prompt in windows ?

Which powershell should I pick, I picked #1 and tried to follow some steps found in the internet. Looks like we have a security issue. ( See pic below )

Lowell SSC Guru Points: 323518 More actions · Answer 10

because you have not used powershell before, you have to do what the error says and set the execution policy.

run this command, and then re-run your script.

Set-ExecutionPolicy RemoteSigned

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!

Sue_H SSC Guru Points: 90860 More actions · Answer 11

mw112009 - Wednesday, July 26, 2017 7:12 AM
Which powershell should I pick, I picked #1 and tried to follow some steps found in the internet. Looks like we have a security issue. ( See pic below )

To not have the permissions denied error, when you run Powershell, right click and select Run As Administrator to change the execution policy.

Sue

below86 SSChampion Points: 11532 More actions · Answer 12

Here is what I've used to search for fields within SSIS packages(dtsx files) stored in the MSDB database.

--Connect to database: msdb SELECT [name] AS SSISPackageName, CONVERT(XML,CONVERT(VARBINARY(MAX), packagedata)) AS SSISPackageXML, CONVERT(VARCHAR(MAX),CONVERT(VARBINARY(MAX), packagedata)) AS SSISPackageVarchar INTO #SSISObjectSearch FROM msdb.dbo.sysssispackages ;

SELECT * FROM #SSISObjectSearch WHERE SSISPackageVarchar LIKE '%ColumnNameHere%' ORDER BY SSISPackageName ;

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 13

Lowell - Wednesday, July 26, 2017 7:18 AM
because you have not used powershell before, you have to do what the error says and set the execution policy.
run this command, and then re-run your script.
Set-ExecutionPolicy RemoteSigned

Sorry , I am out of luck again, Do you see anything wrong that I am doing

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 14

Run the command exactly as Lowell posted. You missed the hyphen out.

John

mw_sql_developer SSCoach Points: 19445 More actions · Answer 15

John Mitchell-245523 - Wednesday, July 26, 2017 8:26 AM
Run the command exactly as Lowell posted. You missed the hyphen out.
John

?