SQL QUESTION but SSIS related - How do we get a list of parameters used by each DTSX file ?

Question

SQL QUESTION but SSIS related - How do we get a list of parameters used by each DTSX file ?

mw_sql_developer

SSCoach

Points: 19445
More actions
June 7, 2017 at 9:44 am

#338077

As you see, we have great list of SSIS packages. I am sure there is some table that keeps track of packages in the SERVER.
Can anyone give me some direction as to how I can write a SQl statement to get the parameters listed in each PROJECT ( I mean each SSIS Package )

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

I believe what you are looking for is the view catalog.object_parameters.
But that will give you the parameters for each DTSX, not for each project. If you want it for the project, you would need to join it to catalog.projects on project_id.

Is that what you are looking for?

Side note - that screenshot doesn't show that you have a lot of SSIS pacakges, you actually cannot see a single package in there. I can see you have at least 4 projects and 3 folders, but each project can contain 1 or more SSIS packages.

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 2

bmg002 - Wednesday, June 7, 2017 2:04 PM
I believe what you are looking for is the view catalog.object_parameters.
But that will give you the parameters for each DTSX, not for each project. If you want it for the project, you would need to join it to catalog.projects on project_id.
Is that what you are looking for?
Side note - that screenshot doesn't show that you have a lot of SSIS pacakges, you actually cannot see a single package in there. I can see you have at least 4 projects and 3 folders, but each project can contain 1 or more SSIS packages.

I cut the screen shot not to show all the projects...
Anyhow, thx for the query suggestions.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 3

BTW-Thanks, that query worked fine.
Since you are an expert on this stuff, let me ask you another question. I see these 2 dtsx files. I see them in SSIDB but I would like to import them locally to my desktop. Is there anyway that you could help me how to extract them ( or copy them ) to my desktop

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

mw112009 - Wednesday, June 7, 2017 3:03 PM
BTW-Thanks, that query worked fine.
Since you are an expert on this stuff, let me ask you another question. I see these 2 dtsx files. I see them in SSIDB but I would like to import them locally to my desktop. Is there anyway that you could help me how to extract them ( or copy them ) to my desktop

Yep, I can help with that too. That is actually a lot easier than you would expect.
What you need to do is right-click on the PROJECT (StratificationExtract) and select EXPORT. Save it somewhere on your local machine. Now rename the file extension to be .zip instead of .ispac. Extract it and you have your DTSX's. If you have a tool like 7-zip installed, it can extract from ISPAC without renaming it to .zip. I imagine that most extraction tools should support this, but an ispac file is functionally just a zip file.
That being said, I strongly encourage you to store those inside of some version control system (GIT, CVS, SVN, etc). Storing it entirely inside the database creates risk.
Also, have you set up environments for your SSIS catalog? if not, I encourage you to do that. You may not like the work it creates initially, but when you need to rename a server or change your data source to be pulled from a data warehouse instead of from live systems, you will enjoy having that environment set up. I set it up on our test SSIS environment for my group and now we can easily move from test to dev for testing running our SSIS jobs (25+ step jobs that call different SSIS packages).
If memory serves, SQL 2016 SP1 offers methods for importing a single DTSX into an existing project without overwriting the entire project, but 2012 only lets you upload projects. So be careful not to overwrite a project that has 2 or more DTSX's with a single DTSX as the others will go away.

Do you know how to roll back an SSIS project in the event you blow it up? That is another good skill to have.

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 5

bmg002 - Wednesday, June 7, 2017 3:38 PM
mw112009 - Wednesday, June 7, 2017 3:03 PM
BTW-Thanks, that query worked fine.
Since you are an expert on this stuff, let me ask you another question. I see these 2 dtsx files. I see them in SSIDB but I would like to import them locally to my desktop. Is there anyway that you could help me how to extract them ( or copy them ) to my desktop
Yep, I can help with that too. That is actually a lot easier than you would expect.
What you need to do is right-click on the PROJECT (StratificationExtract) and select EXPORT. Save it somewhere on your local machine. Now rename the file extension to be .zip instead of .ispac. Extract it and you have your DTSX's. If you have a tool like 7-zip installed, it can extract from ISPAC without renaming it to .zip. I imagine that most extraction tools should support this, but an ispac file is functionally just a zip file.
That being said, I strongly encourage you to store those inside of some version control system (GIT, CVS, SVN, etc). Storing it entirely inside the database creates risk.
Also, have you set up environments for your SSIS catalog? if not, I encourage you to do that. You may not like the work it creates initially, but when you need to rename a server or change your data source to be pulled from a data warehouse instead of from live systems, you will enjoy having that environment set up. I set it up on our test SSIS environment for my group and now we can easily move from test to dev for testing running our SSIS jobs (25+ step jobs that call different SSIS packages).
If memory serves, SQL 2016 SP1 offers methods for importing a single DTSX into an existing project without overwriting the entire project, but 2012 only lets you upload projects. So be careful not to overwrite a project that has 2 or more DTSX's with a single DTSX as the others will go away.
Do you know how to roll back an SSIS project in the event you blow it up? That is another good skill to have.

Is there anyway you could tell me the table/col where dtsx content is stored. I mean is there a varbinary column in some table ( Similar to the table CATALOG in the ReportServer database which holds the contents of each RDL file ( SSRS Report ) )

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

mw112009 - Tuesday, July 25, 2017 11:34 AM
Is there anyway you could tell me the table/col where dtsx content is stored. I mean is there a varbinary column in some table ( Similar to the table CATALOG in the ReportServer database which holds the contents of each RDL file ( SSRS Report ) )

I am not entirely sure that you can. I do not believe that it stores it that whay. I looked through a few tables but didn't see any column that would contain the XML.
If it is possible, I am not sure where to look. All the tables I could think of to look at didn't have it.
This may be different in 2016, but I am running 2012 (and tried finding stuff online) and was unsuccessful at finding that data.

What I normally do if I need into the dtsx is do it through our VCS (git in my case). Basically, pull all the DTSX's out of the VCS and those are just XML so you can use your favorite text search tool (astrogrep is my weapon of choice) and go through everything.

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.