SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


query SSIS-Task-Properties using T-SQL


query SSIS-Task-Properties using T-SQL

Author
Message
WolfgangE
WolfgangE
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 792
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
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14947 Visits: 14396
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
WolfgangE
WolfgangE
SSC-Addicted
SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)SSC-Addicted (479 reputation)

Group: General Forum Members
Points: 479 Visits: 792
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. :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search