Robocopy, Multi-Line Execute Process SSIS Task, or Output Batch File Results to SSIS

Question

I need to robocopy files from one location to another in a SSIS package. Since the folder is on another domain, I need to impersonate another account before I run the robocopy.exe command. I found I can execute a "net use" command to impersonate the necessary user account and then execute the robocopy command immediately afterwards. I don't see any way to do this in an Execute Process command to do this directly, so I use an Execute Process task to run a batch file that has these two commands as separate lines. The downside of this approach is that I cannot read the results of the Execute Process command. So this leads me to three questions:

1. Is there a way to execute a multi-line command in a single Execute Process task?
2. Is there a way to execute robocopy.exe while impersonating another account in one line?
3. Is there a way to write the results of a batch file back to either a variable in SSIS or to the SSIS database log?

If there is a positive answer for any of the above three questions, then I may be able to work out a way to add job success or failure rules based on the results of the robocopy command.

Answer 1

One way I can think of doing this is to execute a batch command ( so you can execute multiple line items) with RUNAS (to impersonate another user account)

You can capture the output of the batch file in a log file and read the contents of that into SSIS using the Script.

I am not sure on how to write to the SSIS log and am interested to see what other developers have to say about that.

Answer 2

I found two round-about methods for getting data from a batch file execution into the database. One method is to add logging to the actual batch file. I haven't tried this yet, but it seems conceptually possible. The other option I have tried that has worked is to put the batch file execution as a SQL Server Agent job step and add flat-file logging. After I did this, I created a small package that scrapes the file for specific messages. I would still prefer a better solution, but this works well enough for now.

Answer 3

This can easily be achived if you have enabled the use of the Extended Stored Procedure "xp_cmdshell" (see Books Online for "Surface Area Configuration").

In the following sample I have build a .CMD file containing all my ROBOCOPY options and simple executes this command file using xp_cmdshell grabbing the output to a table variable (can be a persistent table instead):

Just add a Execute T-SQL statement Task to your SSIS package with the following statement:

/** START **/

DECLARE @cmdfile nvarchar(255) = N'C:\myFolder\myCommandFile.cmd' DECLARE @logtable table ( [RowId] integer IDENTITY(1,1) NOT NULL ,[Message] nvarchar(1024) NULL )

INSERT INTO @logtable ([Message]) EXEC xp_cmdshell @cmdfile

SELECT * FROM @logtable WHERE [Message] IS NOT NULL

/** END **/

Depending on your logging options set for the ROBOCOPY command you can show progress, headers, report or more. See the ROBOCOPY documentation for those options. Also try use the /TEE switch if to grab any console output from the ROBOCOPY command.