Microsoft SQL Server Knowledge Bank: SSIS Execute Process Task a Alternate Approach to Find File

Friday, 30 September 2016

SSIS Execute Process Task a Alternate Approach to Find File

Introduction

In my journey with SSIS, I found a lot of designing pattern and approch to perform a single task.

It may me complex, complicated, simple etc. Continuing my research on SSIS, I am trying to find the thing in simplest manner with minimal resources and process time.

In this article I am trying to demonstrate a simple power of Execute Process tasks of SSIS using simple Batch File.

Lot of my friends are not agreeing with it as they find the Script task is the best fit for that. I want to tell them it’s an approach only that i want to demonstrate.

Scenario

Here, the scenario is quite simple.

Just finding a file in the folder and if found then do the next task be executed others flow must move to another direction.

After finding this scenario, I believe all my developer friends move to one simple conclusion, which is Script Task. Some of the junior developer also thinks about ForEach loop containers.

Well, all we are trying to solve the problem with different approach but anyone think how much process time and resources it takes to solve it.

Here I am trying to demonstrate an alternative approach using the combination of Batch file and Execute Process task.

Hope it will be informative and can be used this approach with BIG GAME.

So What the Batch File I Used

Here I am using a simple batch file to find a file named “Hope.txt’.

Sample: FindFile.Bat

@echo off

if exist E:\Practice\Supporting\Hope.txt (echo 1) else (echo 0)

The code of the batch file is self explanatory. It just find a file named “Hope.txt” from path ‘E:\Practice\Supporting\Hope.txt”. If it found then returns 1 else Return 0.

How I used it with SSIS

Step - 1 [ Control Flow of Package ]