[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.
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:
- Execute SQL Task
- Data Flow Task
- Script Task
- Execute Package Task
- File System Task, Part 1
- File System Task, Part 2
- Execute Process Task
In this article I provide an example of configuring the SSIS Execute Process Task, shown in Figure 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:
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:
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:
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:
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:
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:
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)
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.
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
What is the exact error?
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)
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.
how to run python script ? argument – i given pyhon c:/test/test.py. it’s not working
How to run python scripts using Execute Process Task? I tried using it but did not work.
I’ve successfully run python scripts from SSIS using something like this format on the Process page of Execute Process Task Editor:
Arguments: “C:\Scripts\dosomething.py” -Z
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\”
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.