Post reply

Need to find out which of the SSIS packages stored in msdb reference a specific shared datasource

  • April 15, 2010 at 9:18 am

    #139671

    I need to find out which of the SSIS packages stored in msdb reference a specific shared datasource, dtsrc1.

    System view sysssispackages does not seem to be the way to go.

    Anyone have any other way?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • April 15, 2010 at 11:40 am

    #1152319

    If you can query the system tables then this is clearly the best method but I have no idea if this is possible.

    See this as an alternate method.

    http://www.databasejournal.com/features/mssql/article.php/3734096/Using-dtutil-to-copy-SSIS-packages-stored-in-SQL-Server.htm

    If you can export all your packages out to files you should be be able to search the dtsx files.

    I have never done this, there may be a better way but I thought I would offer this as a suggestion.

    Good Luck

  • April 15, 2010 at 12:00 pm

    #1152343

    Marios Philippopoulos (4/15/2010)

    I need to find out which of the SSIS packages stored in msdb reference a specific shared datasource, dtsrc1.

    System view sysssispackages does not seem to be the way to go.

    Anyone have any other way?

    Thank you, I also think this is the only way;

    I just put this out there in case there is another solution and because I find it frustrating that metadata support is so poor in SSIS.

    Fortunately I only have about 15 packages in my SSIS solution - all using shared data sources - so I just bit the bullet and searched them in BIDS one by one.

    What would I do if I had 100?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • April 15, 2010 at 1:07 pm

    #1152383

    You could build a VB or C# app to open the packages one-by-one and examine the connections collection. I *think* you can read them directly from the server but I haven't done that so I am not going to take a solid position on that.

    CEWII

  • April 16, 2010 at 8:51 am

    #1152895

    Elliott W (4/15/2010)

    You could build a VB or C# app to open the packages one-by-one and examine the connections collection. I *think* you can read them directly from the server but I haven't done that so I am not going to take a solid position on that.

    CEWII

    Thank you, maybe there is a tool out there that does that...

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply