Search code examples
sql-serverssisetlsql-server-data-tools

How to Copy SSIS Package from MSDB Database to File System using ssms or ssis Package

Is there a Way to Copy SSIS Package from MSDB Database to File System using SSMS or SSIS Package.

My attempts where:

  1. using dtutil with xp_cmdshell in SSMS QUERY

    EXEC xp_cmdshell 'dtutil  /sourceserver  SERVERNAME\INSTANCENAME /SQL "\TestingData\Package" /copy file;"c:\Package.dtsx"'

result:

enter image description here

NOTE: no problem when using xp_cmdshell without dtutil since i made a successful 'File Copy'.

  1. my second attempt was the same but using SSIS component Execute Process task.

enter image description here

BUT the result was:

Error: 0xC0029151 at Execute Process Task, Execute Process Task: In Executing "C:\Windows\System32\cmd.exe" "dtutil /sourceserver servername\instancename /SQL "\TestingData\Package" /copy file;"c:\Package.dtsx"" at "", The process exit code was "1" while the expected was "0". Task failed: Execute Process Task

Solution

  • Suggestions

    There many things to take into consideration:

    1. In the first screenshot, the output your are getting is that Access is denied, so first check that you have write permissions on C:\ drive, or try copying to another path.

    2. Try reordering the parameters you are passing to the dtutil tool and remove the \ before the package path:

      EXEC xp_cmdshell 'dtutil /SQL TestingData\Package /sourceserver SERVERNAME\INSTANCENAME /Copy File;"c:\Package.dtsx"'

    3. You must have a windows authentication on the SQL instance you are connecting to, in the command above , so if you don't have this type of authentication, try using an SQL authentication:

      EXEC xp_cmdshell 'dtutil /SQL TestingData\Package /SourceServer SERVERNAME\INSTANCENAME /SourceUser Monkey_User /SourcePassword P@$$word /Copy file;"c:\Package.dtsx"'

    References