SSIS

• Austin_123

  SSCommitted

  Points: 1648

  More actions

  October 15, 2015 at 9:43 am

  #321541

  I am trying to get some details for one of my SSIS migration project. I need to find all of the SSIS .dtsx files which are located on file system storage and they are getting executed by the SQL Instances.

  I have connected my 100 instances through registered servers and ran below query to find out the physical .dtsx files but this is not providing me complete details.

  USE MSDB

  GO

  SELECT

  sj.job_id as JobId,

  sj.name as JobName,

  sjs.step_name as StepName,

  sjs.Command as Command

  FROM sysjobs sj

  INNER JOIN sysjobsteps sjs

  ON(sj.job_id = sjs.job_id)

  WHERE sjs.Command like '/FILE%'

  GO

  Is there any way I can get the list of all physical .DTSX stored on file system in below format?

  SSIS Location SQL Instance

  E:\SSIS\BankXYZ_Load.dtsx Servername\Instance

  Thanks in advance!

  Austin

• JustMarie

  SSCertifiable

  Points: 7771

  More actions

  October 22, 2015 at 11:52 am

  #1834994

  Try using a Powershell script since you're doing a file system search.

  Get-ChildItem -Path "[path]" -Recurse | Select-String -Pattern "[pattern]" | Out-File "[output file name]"

  This will search recursively so start at the highest directory and let it do its thing.

  Of course someone who is better with Powershell will suggest improvements. 😛 I'll gladly snag them for myself.