SSIS - How to check if a File exists

Scenario:

We have a scenario, in which we have to check if a file exists in a folder. 

If the file exists , delete it and if the file does not exists, create a new one and append some data to it. 

Solution:

We can achieve this, using a simple script in "Script Task" in the Control Flow. 

Lets say, 

File name: Commands.bat 

File Path: I:\Commands.bat

Moving on to how to achieve this.

Step 1: 

Create the following variables at package level.

v_fileName = "Commands.bat" (Give the name of the file that you are checking for)

v_filePath = "I:\" (Give the folder where the file has to be located at)

Step 2: 

Drag and Drop the Script task from the tool box to the work space(control flow) and click on the bubble for ReadOnlyVariables.

Step 3: 

select the input variables for the Script task as shown below. 

Click on OK and then click on Edit Script

Step 4: 

Copy the code which is shown below

 Step 5: 

Execute the task to see the file created. 

Before Executing task

After Executing ScriptTask

SSIS - Executing a set of Command Line Arguments

Scenario: 

We had a requirement, in which a set of command line arguments had to be executed from an executable file (like cmd.exe, curl.exe etc. ) as part of an SSIS package. 

Solution: 

We can achieve this using a batch file and Execute Process Task. Lets see how we could do this in a simple way. 

Step 1: 

Open Business Intelligence Development Studio(BIDS).

Create a sample project by clicking on File - > New - > Project . Select Integration Services Project . Enter a name for the project. 

Create New Project

Step 2: 

Drag and Drop the Execute Process Task from the tool box to the work space. 

Drag and Drop Execute Process Task

Step 3:

Create a Batch file (.bat ) that contains the set of command line arguments that you want to execute. 

Create a new text file , enter all the commands, save the file name as Commands.bat

Set of Arguments

NOTE:
1) The first command has to be the Change the directory to the executable file you are using (Here, curl.exe, in your case it could be cmd.exe or any other executable file)


2) There needs to be line spacing between your arguments. If there is no line spacing, the batch file does not get executed as expected. 

Step 4: 

Configure the Execute Process Task in BIDS. 

1) Under "General" Tab, enter the name

2) Under "Process" Tab, for Executable , browse to the executable file i.e. batch file that has the arguments by clicking on the bubble. 

3) click on OK

Step 5: 

Execute the package to see the arguments in the batch file to get executed . 

Navigate to the curl.exe path to see the sample.csv file created with the data. 

P.S: 

The arguments in line 2 and line 3 are there to import data from a web page and append to sample.csv file.