January 14, 2013 at 12:48 am
Hi there,
we have several SSIS-packages on our SQL servers, usually stored in the msdb. In these packages you find "execute sql"-tasks as well as other tasks using T-SQL-statements.
We have to change some tables in our database and need to know if they are used in the SSIS-packages. Unfortunately some of the packages are quite old and do not use stored procedures for data access but ad hoc queries.
Is there a way to get this information without writing a C# program?
I wonder if the ssis package tasks and their properties are stored somewhere in the system tables so I can query these properties using T-SQL.
Thank's a lot, WolfgangE
January 14, 2013 at 7:28 am
The SSIS package definitions are stored in msdb.dbo.sysssispackages. Because the definition is XML you can cast it to the XML data type and then operate on it using XQuery. A carefully crafted query could deliver you all the SQL Statements contained in every package. However, many times SSIS developers will use Variables and Expressions to build SQL statements at runtime so I would recommend you only view the information in the table as an initial discovery tool and not the authoritative answer as to whether something will be impacted by a change.
SELECT CAST(CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX)) AS XML) AS xml_package_definition,
*
FROM msdb.dbo.sysssispackages
WHERE CAST(CAST([packagedata] AS VARBINARY(MAX)) AS VARCHAR(MAX)) LIKE '%%';
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 14, 2013 at 7:39 am
Thank's a lot. I already found this table but I did not realize that the one column contains the whole package. The hint of queries built at rune time is a good one too. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply