Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

query SSIS-Task-Properties using T-SQL Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 12:48 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:41 AM
Points: 197, Visits: 729
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
Post #1406581
Posted Monday, January 14, 2013 7:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 23, 2014 8:34 AM
Points: 7,097, Visits: 12,601
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
Post #1406720
Posted Monday, January 14, 2013 7:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:41 AM
Points: 197, Visits: 729
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.
Post #1406732
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse