“How do I…?”
I get questions that begin this way. Today, a friend reached out to ask how to search internals of SSIS packages deployed to an instance of the SSIS Catalog. My response was, “I don’t know, but this is how I would learn the answer.” I sent the following query:
Select OBJECT_NAME(id) as [name], *
Where text like '%object_versions%'
The query is rudimentary; it returns a list of views, procedures, and functions that include the text “object_versions,” which is the table in the SSISDB database – the database used by the SSIS Catalog – that stores SSIS project binaries (in the object_data column). The query also returns a snippet of the DDL (Data Definition Language) for each object in the text column.
If I had this question, I would begin answering this question by learning how the SSIS project binaries are opened by SSISDB database stored procedures.
Is this the only way to answer this question? Goodness no!
Is it one way to answer the question? Yes. Yes it is.
Often, the path to a solution begins by asking one good question. I should blog about this. Oh wait…