SSIS Tutorial Part 25-Load Multiple Excel Files to SQL Server Table & Archive them with Datetime

In this video you will learn how to load Multiple Excel files to a SQL server Table and archive them after loading them.

Expression used for video to get date_time.
Replace(Replace(Replace(SUBSTRING((DT_WSTR,50)(GETDATE()),1,16),"-","")," ","_"),":","")+".xlsx"

How to load Multiple Excel files to SQL Server Table
How to Load Multiple Excel files to SQL Server Table and Archive with datetime added to them.

In this video you will learn
How to use foreach Loop Container
How to use Excel Source in SSIS
How to use File System Task to rename and move files.


Check out our Step by Step SSIS Tutorial
http://www.techbrothersit.com/2014/12...

Twitter

https://twitter.com/AamirSh48904922

Facebook
https://www.facebook.com/TechBrothersIT

---

Closed Caption:

come to tech brothers without me today
we are going to answer this question and
perform a demo
how would you load multiple excel files
that was equal server table and archive
them with the daytime
after loading so what I have here I have
some files and they are called asia or
europe and north america
each file has the data on sheet1 and
they have the same column so it has ID
and name
so there are a couple of Records for
asia and then we have a couple of
Records for Europe and then there are
couple of Records for north america
so what I'm trying to tell you here the
sheets are the same with the matter that
information so in an ID on the name are
the only columns and then we are also
using the same sheet name on each of the
file and then
now let's go to SMS sir and take a look
what we have here
I have a prepared at the ddl for the
table so we have a table customer ID
interior and sane person worker hundred
so I'll i have carried this table that's
where we are going to load the data from
those excel files and if tomorrow there
would be a new file we don't have to
worry
making any changes as long as you follow
the same structure of the file it has it
it should be having that on the sheet 1
and the column should be ID and name so
we can add hundreds of files and this
can be used to load all history files up
and and we don't have to make any
changes in the packages load multiple
files
let's go to the ssdp sequel server data
tools and create a new package
so let me drop this one and here right
click and say new ssis package and we
can name just one load external files
and let me get the the folder pattern so
we can provide to be for each loop i'm
going to create a variable Y our command
variable always a because we can provide
the configuration value according to d
environment
let me give input for the name or input
folder not name is its structure or the
pad so keep it string and then we have
value so this is our input folder
the next part is we need to for each
loop so we can read up the files of one
by one
let's go here and then go into
collections
while each fire for each violence and
murder that's fine Vanessa and here we
want to pass the value of that variable
so we are going to set directly equal to
the input folder variable
ok
it ok now go to variable mappings and
here we are going to say well done
the filename and extension in filing
variable so on each of the operation is
going to go and read the file name and
then and extension for us and save it to
a file name variable
let's go to collections here we do not
want fully qualified pattern we have a
partial or folder path data is in didn't
put folder
I just want to see the name and
extension in the file name variable so i
have selected the name and extension
here and then we have mapped to the file
name variable it ok so if we run this
fortunately loop now it is going to loop
through the folder and give us one file
at a time with the filename and
extension
now our next goal is a to load the
sponsor
so let's go to data flow task here and
inside the data flow task will be
reading the file from the exercise will
do you
in Excel source here let's make a
connection
browse to the folder select any of the
file as that this will be overwritten
that the very first time we are making a
connection so it can see the matter that
information it would know okay i'm
reading ID and name so you can make
connection to any file doesn't really
matter
so let's take up north america and the
very first row has some column name so
fine and our excellent where you need
two thousand salmon it okay
now select the sheet name so we have
sheet 1 and we have columns ID and name
next part is a let's do this data
conversion as the exit is going to read
the name in the workout - 55 and the
decimal um
by default kind of thing it to start if
there is a column without work ahead
with the string values it is going to
read as a and work hard and in our
destination and we have a value of the
data type of that column is one HR
instead of full and watch are so XL is
reading and watch our and we are
converting that to be watch are so let's
go here and now look here instead of
unicode we want to be string desa watch
our and then change this one to two
hundred and here we can name anything
what we like we can call it dr better
conversion DC and say a name
we are all set - let's bring other
destination here already be destination
connect with this one and map the
columns create a connection
I have a connection already so it is my
server name and then database name if
you want to create a new one you can
create whatever your table will be
existing so it okay
and then you can go fast lord
and select the table name we have
customer good mappings and that idea is
mapped to ID as it was the same name
here we have seen in person but here we
have a DC name so if the names are same
and then put on the output side they
will be map automatically as columns
if not you can do some manual work and
map your input columns for the output
columns as you would know that a
relationship between them so it ok now
what is happening here
our x ella is a no the connection is
made to the north america file but on
each of the tradition it is going to be
really different filename and extension
one thing we can do if we had done this
package now it is going to write the
data to the sequel server table three
times from the same file because there
are T files in the folder
what we want to do we want to kill
change the file name of every time that
data flow runs up
so let's go to connection manager and go
to properties up and here we can go to
the expressions hit the the butter right
next to it then go to properties and we
save fly XL file pattern here we are
going to make a changer and use a the
input folder and append or add the file
name to it and evaluates expression
it looks good we do not have any files
saved in the file variable as of now so
we don't see that one here about that
when the package is going to run this
will be provided from the for each loop
it okay
it ok and the sector that the late
validation to true because right now you
know that is no well you set for just a
connection manager and it will to add on
the validation of and we will run the
packager so we want to set this one to
on
validation to true so we do not want
this to any data and the initial start
up the of the packages we want it will
validated after the for each loop
container will be we having a Hello will
have read the filename and extension
also set the same property 2d data flow
task the level addition to the true
so we do not want to any date that's
their the XL connection manager or the
data flow on the start of the package
you will back there will be valued at at
time of execution
so now if we run this package of this
will read month each file know in one
operation and load that to the table
let's take a look what we have in the
table we don't have any data in the
table
let's run the package and we can see
that it is running multiple times
stop your package go to the sequel
server table and run the query so it has
a loaded a 60 record to record from each
of the file so that's fine this is
working great now the next part is we
need to book these files from source
folder to the archive folder as well so
let's go and take the archives or folder
pattern and saving variable now create a
new variable called arc ipad order and
here
save that information
now you are archived remove these files
we need a complete path that should be
consisting of four archive folder
plus the file name plus the the date and
time appended to it and that's what we
want to create so we want to create the
archives full battle wouldn't pattern
that should be consisting awful our art
I patted plus the file name
so let's go two expressions archive
folder we have it then we want to add
the file name but we do not want to add
the extension of the file
so let me go back here it ok for now and
I'm going to provide any random name
that's . excel as exit so then I
evaluate expressions i can see what
exactly is happening
so go to our castle floor girl full fat
one more time and click here now what we
see we see that what is happening here
so we need to replace the extension here
because we don't know we don't want to
use the extension here . x LOL SSX with
the northern with the nothin
so we don't want to be just one of the
replace the dot XLS sir
with the blanket so I remove that part
now the next part is that we want to add
a underscore to it so we added
underscore to it
i have written expressions are for the
to get the date and time i'm going to
show you i don't want to spend time in
five minutes you're right expressions so
it is a getting the data value from get
function and then
using substring and I'm casting it and
the desk the expression i will use our
passes expression in the description as
well so you can take it from there
let's add this one so now we're seeing
what we have here we have to put ad
so now we can see that it completed the
entire string of we have a folder path
where it needs to be archived and the
new name of the file that will be actual
file name is your not permit or whatever
then the year and their part will be
added to it and then the time part will
be edited and the extension is dot XLS
it so expression is ready
one more thing we have to do we have to
have a input foot pattern because i know
we have an input folder and we have a
file him but we do not have a complete
better
so we want to create that one as well
that should be consisting offer and put
folder
plus size of the file name and these two
variables will be using the file system
test so we can move the files from
socially destination or from input
folder to the archive folder let's bring
the file system task here and now on
each of the situation we want to load
the file and then move it
so here in the copy file we will be
using the rename file that will be blue
water file a desert rename it a while it
will look
move it so it's or so bad variable yes
sir
and what is that this is where we are
going to provide input control pad
because it has file name and the photo
structure so it will provide that
variable then its destination and pass
is also variable true
and then what we provide here archives
full pattern so it has the folder path
back / file name then dad part and then
time part and art xls sir
so that's what we want to save the file
and you want to override it if the file
exists true
that's probably not going to happen as
it will be adding the time part with
every time we're under file so besti
let's keep it up we want to know what I
did
if the file is existing there it ok now
let's go back and take a look
we do not have any file sitting in the
archive folder and let me go back to the
table
let's try to get this table and get
table so we don't have any red cursor
and that our each file has 22 rate goes
up so let's run the package should load
sixteen acres that as well as movable
those files to the archive folder
ok so we can see that on each iteration
there looked at is loaded and then the
fire is a mood
Louise stop the package come back to the
archive folder so files are moved and
there they are renamed the ads when so
let's go to input folder and the files
are not there so they were here we have
loaded them and then we have moved them
to the archive folder by adding
date-time part two of them and data
should be loaded in the table as well so
lesson number select committee on the
table and we can see that the records
are loaded up from three files of this
table
so just one more point i would like to
say and i have explained that in the
start of the package we want to just
make sure all the files have this theme
on structure and that's all this package
is going to work if you have different
structures and that is saved on the
different sheet so this is this package
is not going to work or so to make this
work you have to have data on sheet 1
for each of the file and then the matter
data or the column structure on the
excel file that should be the same
so thanks very much for watching this
video and i will see you guys in the
next video