SQL SERVER – The Basics of the Execute Process Task – Notes from the Field #084

[Note from Pinal]: This is a new episode of Notes from the Field series. Every time I give an introductory note, however, this time there is no need of intro note. This note is from Andy and as we all know he is amazing person when we have to understand the fundamentals. He has written this blog post with such an interesting way that you must read it to understand the very basic of the file system task.

SQL SERVER - The Basics of the Execute Process Task - Notes from the Field #084 andyleonard


Many data integration scenarios involve executing some other process, whether starting a custom application or performing an operating system operation.

Remember: SSIS is a software development platform. With “SQL Server” included in the name, it is easy for people to confuse SSIS as a database tool or accessory, but Control Flow Tasks put that confusion to rest.

SSIS provides several Control Flow tasks. Here is a list that provides a good approximation of which tasks I use most, from most-used to least-used:

In this article I provide an example of configuring the SSIS Execute Process Task, shown in Figure 1:

SQL SERVER - The Basics of the Execute Process Task - Notes from the Field #084 notes-84-1
Figure 1: SSIS Execute Process Task

As with the File System Task, the Execute Process Task provides yet another way to implement an SSIS Design Pattern for source file archival via file compression. When you first open the Execute Process Task Editor, you will note several properties in the property grid, as shown in Figure 2:

SQL SERVER - The Basics of the Execute Process Task - Notes from the Field #084 notes-84-2
Figure 2: SSIS Execute Process Task Properties

An important property is the Executable property which holds the path to the application or process you wish to start with the Execute Process Task. In this case, I am going to start the 7-Zip command line executable to zip a data file. 7-zip is a free file compression utility, and the command line utility is pretty cool. On my system, the 7-Zip command line utility is located at “C:\Program Files\7-Zip\7z.exe” so I configure the Executable property of the Execute Process Task as shown in Figure 3:

SQL SERVER - The Basics of the Execute Process Task - Notes from the Field #084 notes-84-3
Figure 3: The Execute Process Task Editor with the Executable Property Configured

The Arguments property allows me to specify command line arguments to the executable. For 7-Zip, the “a” argument informs the application that I want to add files to a compressed file. The text following the “a” argument specifies the name of the compressed file. The argument that follows the name of the compressed file configures the file (or files) to add. My arguments property reads:

a E:\Projects\7Zip\data\archive1.7z E:\Projects\7Zip\data\test.csv

These arguments tell the 7z.exe executable that I want to add the E:\Projects\7Zip\data\test.csv file to a compressed file named E:\Projects\7Zip\data\archive1.7z, as shown in Figure 4:

SQL SERVER - The Basics of the Execute Process Task - Notes from the Field #084 notes-84-4
Figure 4: The Execute Process Task Editor with the Arguments Property Configured

I can configure other Execute Process Task properties. For example, I choose to hide the command line window for 7-Zip when it executes. To do so, I set the WindowStyle property to Hidden, as shown in Figure 5:

SQL SERVER - The Basics of the Execute Process Task - Notes from the Field #084 notes-84-5
Figure 5: The Execute Process Task Editor’s WindowStyle Property

The SSIS Execute Process Task is now configured to compress a file. Let’s test it! Click the OK button to close the Execute Process Task Editor. Press the F5 key or select SSIS->Start Debugging to test your work. My result is shown in Figure 6:

SQL SERVER - The Basics of the Execute Process Task - Notes from the Field #084 notes-84-6
Figure 6: Successful Test Execution of the SSIS Execute Process Task

Viewing the source and destination directories, we see the file was successfully moved – shown in Figure 7:

SQL SERVER - The Basics of the Execute Process Task - Notes from the Field #084 notes-84-7
Figure 7: The File, Compressed!

As I stated earlier, the SSIS Execute Process Task is powerful, flexible, and robust. This article has demonstrated another way you can use the Execute Process Task to compress files for archiving. Archiving files after loading the data they contain is a common practice in data integration.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Security Auditing With ApexSQL Audit
Next Post
SQL SERVER – How Many Line of Code Do You Have in Database?

Related Posts

10 Comments. Leave new

  • Have you tried other executables, such are calling robocopy. I’m finding that it just plain will not run when called. If you know a trick to getting it to run I would really appreciate it.

    Reply
  • Assume that Im having two connection (A and B) in Connection A my SSIS Package is built , if i need to call
    Visual Studio(.exe) file Program in connection B which process i need to execute, kindly guide me

    Note: I tried with Execute process task whereas in connection B my project folder is in share only , but it could not find the path it shows error

    Reply
  • I need to call the server 2 exe file in server 1 which task i must use ?
    Error i am getting when i execute : could not find the part of the path in Command prompt while executing the exe
    (The exe file is in share folder)

    I tried to use Execute Process Task Editor window inside that Executable i have assigned the path like (\\server name\Folder name\.exe) ,
    and in the working directory (\\Server name\Folder Name)

    Reply
  • Has anyone ran into issues with execute script with IE 11 and SSIS 2008? We have our packages create batch files using script and execute the batch files using Execute Process task. This process has been working fine until IE11 has been upgraded. Since IE, we see a pop-up for Interactive Services Detection on SSIS server treating the created batch file as un-trusted publisher and asks it want to Run or Cancel.
    Any clues?

    Reply
  • how to run python script ? argument – i given pyhon c:/test/test.py. it’s not working

    Reply
  • How to run python scripts using Execute Process Task? I tried using it but did not work.

    Reply
  • I’ve successfully run python scripts from SSIS using something like this format on the Process page of Execute Process Task Editor:
    Executable: C:\Python34\python.exe
    Arguments: “C:\Scripts\dosomething.py” -Z

    Reply
  • what does it mean by -aoa, -p, and -w in argument expression?

    I have this expression: x \\DEVFS04\data\Care1st\Inbound\CAP_MMR\ -o\\DEVFS04\data\Care1st\Inbound\CAP_MMR\ -aoa -p”” -w”\\DEVFS04\data\Care1st\Inbound\CAP_MMR\”

    Reply
  • Hi,
    I have Package with Execute Process task that gets the files from SFTP using Putty.It works file locally. But when i execute the package from Integration services catalog, the package hanging at the Execute process task.
    I am using Sql server 2017. Please help me in resolving this.

    Reply

Leave a Reply

Menu