February 10, 2022 at 8:44 pm
A developer is asking me to provide a list of all the "queries" (aka sqlcommand) as it is in the tables there. He is asking for ALL of the queries from ALL of the packages...
Is there an extract I can run against the SSISDB to render this list of queries? (the developer was pulling from: ssisdb.catalog.event_messages with (nolock) inner joined with ssisdb.catalog.event_message_context
thoughts?
February 10, 2022 at 9:25 pm
he/you can extract all packages to an .ispac, extract the .dtsx files from this (its just a .zip file) and then search/parse the packages for it. - that will give what he needs.
February 10, 2022 at 9:42 pm
thx for this feedback.. also found this query which we're trying to expand to find slqcommand:
USE SSISDB
SELECT pr.name AS [ProjectName], pr.description AS [ProjectDescription], pr.last_deployed_time AS [ProjectLastValidated], pr.validation_status AS [ProjectValidationStatus], op.object_name AS [PackageName], op.design_default_value AS [DefaultConnectionString]
FROM [internal].[object_parameters] op INNER JOIN [internal].[projects] pr ON pr.project_id = op.project_id AND pr.object_version_lsn = op.project_version_lsn
WHERE op.parameter_name LIKE '%.ConnectionString'
February 14, 2022 at 1:10 pm
If you are using Visual Studio, or github with a copy of the packages in your local workspace, then a search utility should yield the list for you.
I'm a fan of File Locator Lite and File Locator Pro, as that utility supports some regular expression capability to let you look for the pattern of select followed by one or more characters, etc, followed by from followed by zero or more characters, etc.
Luther
February 14, 2022 at 1:29 pm
Alternatively have a look at "Get packages from SSIS Catalog"
it uses Powershell to download the packages
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply