SQL Server Integration Services Tutorial -Check If File Exists in a Folder in SSIS Package

SQL Server Integration Services (SSIS ) Interview Question " If you need to check that a file exists in a folder, which task(s) will you use? "

To Follow Step by Step SSIS Video Tutorial
http://sqlage.blogspot.com/search/lab...

---

Closed Caption:

that brothers Adama today we are going
to answer this question and perform a
demo if you need to check the data file
in this new folder which task or tasks w
user so this is very common and are you
there sometime we put a file in the
folder and then that can be to your file
depending on that slider you want to run
some so done past in ssis packages so
let's create the ssis package you by
going to the bids or SS BTW i am in an
ssdp sequel server data tools and i'm
clicking on the projector and say create
new ssis package now we can rename this
1 i'm going to rename this one PKG check
file exists to make sure the file exists
oh we need to have a folder path of it
from there we need to check the file of
existence of a file so we have our
folder here that can change in different
environments so i'm going to use this
file path variable and one for what if
we deploy our package from deputy qar
USB or SI t we will be able to pass this
value of you know folder better to be
valuable
by using the configuration so i'm going
to create a variable here called folder
path also the filename we can change you
all the time and we we might want to
consider that as well instead of just a
reading every file from here we can
create a variable and we're going to
call it filename and that's where we can
always pass the value for your file we
want to check for so here in this case
it
we're checking for customer file dot txt
i'm going to copy that one and paste it
here
now the next part is a take a look in
the set of tasks so we have here and see
which one performer can perform that one
if we take a look here all those tasks
we do not see a task that can give us if
the file exists or not so we have to use
a plus the script task here to perform
that one so here a bring the script ask
also as we want to pass the value back
telling the file exists or not we want
to have some flag i'm going to use the
let's say we want to run this data flow
task if the file exists ER and if the
file does not exist we do not want to
read this data flow so no we will be
same a value 0 awful all file exists or
not exist in some of the variable and
i'm going to call that variable called
five existed flag and if the file will
exist
this is the set the value of this so
variable will be set to one and that we
will use in here in presents constrained
and that's all we'll run it we are going
to go here in the constraint sir and
that change to the expressions and say
file existed flag is equal to one if
that will be true
that's a that's where our data flow task
with runner other wiser or data flow
task will not run so i have put the
conditionality even we didn't set the
value yet so i'm going to set the value
in the script ask now let's go to script
task could open the script has been
double clicking and then here we can
select the language we want to use we
asked the sharp or visual basic i mean
when i use the C sharp here and here
define the variables from which you want
to read the values so i'm going to read
the folder path and filename so I cannot
check the file exists or not and then
i'm going to write the value for the
file exists flag so that's that's what
I'm gonna set in the script task or edit
the script task just get taken arm use
it for seconds and can come back now
we'll start writing other code here that
name
indian ale and a person here we have
names faces already some of them are
coming as a default so what we have to
do is we are dealing with the files that
we will be adding one more system died
oh so we can use the phone functions
from that namespace next part but we'll
be doing here that will come into the
main part of the program and then
writing our code so what we have here i
have the folder path and filename of a
about it and that's a 10 from that's a
location from where I need to check the
file so i'm gonna call this one stronger
while I can make it maybe phyla slippery
path and that should be equal to BTS the
dart variables and the invariable we
have a users user file folder path and
then what we have not value to spring
and then I'm going to add that value
with the let me hear will be adding the
value also available that that is DTS i
can copy this whole thing here
right here so we're building the
complete back for the file here will be
filename now we have saved the that
excuse me we have to have the full of
old filed by a better indeed about a
variable here and now what we can do we
can put some conditions before and
started here let me see
and then we can say file . exists if
that's true and we have to provide of
bad they're divided file little better
and then close it and if that's true
what you need to do you need to say
alright and what we will be doing here
will be writing BTS BTS . variables and
then we have to provide the variable
name here we can start the year
appearances and i can copy this one just
have to rename the variable so I'm going
to label reading the variable and call
it to find existed
this is too flat and then i can set this
value to 1 and that's it so no excuse me
so now when we were another pad or ssis
package and special escaped my part of
that it is going to go to that folder
and take a look for that file if that
file exists what is going to do it is
going to set the value of file exists
variable to do one and that we have used
in presence constrained to run our data
flow task or any other next test
let's save the cord and close this
window and hit ok now let's run our ssis
package and see if it is working so we
can see that the first test when
completed and then the value are set to
one for the flagyl and that's all that I
look around successful as well
now stop this package you and go back to
and change the name of the file to
underscore one in this case that now the
file name is changing and that file what
we were looking for we're looking for
customer file . PhD that is not existing
there anymore if I will run my ssis
package now it should not actually under
data flow task so you see that here the
data protester run because the value in
the variable is a not equal to a
customer file
so it doesn't what we have here in your
presence constrained the file exists
flag is not set to 1 as the value water
not not equal 3 customer file and now if
I want to take a look i want to change
the filename here i can change it to
underscore one and that's all we will be
telling ok we are looking for this file
and if I run this necessary is backing
now it will run fine because the file
does exist with this name and I have
changes in the packet okay we're looking
for this filename and go ahead and run
it and now that we should run so that
that's the way you will write your
script asked to still set the flag or
file exists as a variable and then using
the president's constrained to make sure
that file is this before you done other
tasks
thanks so much for watching this video
and i will see you next video