SSIS provides many interesting tasks and components in its kitty to work with. This post is inspired by one of the question asked in a popular SSIS forum. The user wanted to run the command line program and then wanted to write some commands into Command Line prompt window to achieve some specific task.
In an ideal scenario SSIS is not the right choice to develop this type of solution because it is best suited to ETL (Extract, Transform and Load) of data. Nevertheless with SSIS one can achieve this type of task. In this post we will learn how we can run Command Line Program and pass arguments to Command Line window.
Let us consider a scenario where we want to copy a file from one directory to another directory. SSIS has many tasks available which we can use to achieve this but we will see how we can use Command Line Program to copy files.
To copy a file from one directory to another directory using Command Line Program all we have to do is to launch the Command Line Program and write the Copy Command. For example I want to copy xyz.txt as NewXYZ.txt then I have to write following command in Command Prompt window.
With SSIS we will automate this task. We will let SSIS to call the Command Line prompt and we will pass the file name as parameter from SSIS.
- SSIS: Read public data of Twitter Users?
- SSIS: How to pull Tweets from Twitter?
- SSIS: How to pull Currency Rates from European Central Bank
- SSIS: How to pull Stock Quotes from Google Finance
- SSIS: How to load data into SQL table from Google Spreadsheet?
- SSIS: How to Download images from Picasa album?
- SSIS: How to Load images into Picasa web album?
We will start with a Batch (.bat) file creation. The objective of this file is that we will write all the commands that we want to execute on the Command Line prompt window. We will pass this file name as argument to command line program.
@echo off
D:
copy %1 %2
exit
The two parameters placeholder in this batch file is defined as %1 and %2. To define the arguments place holder in a batch file we use %1, %2 and up to %9. So we can define 9 arguments in a batch file.
In SSIS we start with defining three variables. The first variable is comd which will hold the batch file name along with attribute /k; which indicates to command line prompt to run the file as it starts. The second and third variable is srcFile and tgtFile which holds to source and destination file name.
In Control Flow tab we have added Execute Process Task.
We double click on the Execute Process Task to launch the Execute Process Task Editor window. In the Executable property we have set the value C:\Windows\System32\cmd.exe; which is the location Command Prompt Window is located in my machine.
Next I clicked on Expression and added expression using three variables defined above.
After setting up the SSIS I run it and it run successfully and copied xyz.txt file in D drive location.
So this is how we can run Command Line Program window and pass arguments to it
