Post reply

datasources of ssis

  • July 15, 2020 at 4:15 am

    #3770022

    How do I get all the datasources of all of my SSIS packages stored in msdb database ?

    How do I query it ?

    How do I list SSIS packages and the relevant SQLJobs which run it.Which tables do I look for ?

    • This topic was modified 4 years, 4 months ago by  mtz676.
    • This topic was modified 4 years, 4 months ago by  mtz676.

  • July 15, 2020 at 8:41 pm

    #3770368

    for the jobs that run SSIS packages, my approach is to script out the job steps table joined to the jobs table.  Something like:

    USE [msdb]
    GO
    SELECT 
    name, step_name, command 
    FROM [dbo].[sysjobsteps] JOIN [dbo].[sysjobs] ON [sysjobs].[job_id] = [sysjobsteps].[job_id]
    WHERE command LIKE '%dtsx%'

    The above will give you all jobs and the corresponding job steps that call a dtsx (ssis) package.

    Looking at the query text, you can also determine the data source of the job step.  I personally would not rely on the data source of the SSIS PACKAGE because you can override that in the job.

    NOTE - the above query I ran against my system where I am using an SSIS Catalog.  I believe it should similar or identical if you are not using an SSIS Catalog, but I did not test that.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 2 posts - 1 through 1 (of 1 total)

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