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
E:
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 ]





Step - 2 [ Create Variable ]





Step – 3 [ Use Execute Process Task ]




Executable : E:\Practice\Supporting\FindFile.bat
StandardOutputVariable: User::vResultSet


Step – 4 [ Precedence  Constraint Editor ]




@[User::vResultSet]=="1\r\n"




@[User::vResultSet]=="0\r\n"



Hope you like it. If you have any other alternative approach, please share your knowledge.



Posted by: JOYDEEP DAS