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: Thursday, April 10, 2014 5:49 AM
Points: 187, Visits: 699
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: Today @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
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: Thursday, April 10, 2014 5:49 AM
Points: 187, Visits: 699
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