Store Images,Documents,PDF (BLOB) Files Into SQL Server Using SSIS

https://www.youtube.com/user/masterke...
A word of caution do not confuse it with Import Export Wizard of SQL Server,
http://www.youtube.com/watch?v=8g7_F3...
Here I had walked you through how to upload various files and store them in a SQL Server Table, Import column transformation allows you to load file (.txt, mp3, pdf, txt, jpg etc) into a table as a ETL process via a Data Flow Task, of SSIS.

---

Closed Caption:

hi friends so I'm here again with my
next set of transformation for you
so in this session what i am going to go
across is the import column
transformation that we have and will be
learning import and export column
transformation
do not mistake it with the import/export
resort that you have for importing the
data from various into platform sources
and destinations
this is a part of transformation in the
data flow task if you would know
so basically for to let me just walk you
through a brief about the import column
transformation today so important
transformation is basically event you
are trying in your exercise package when
you are trying to import files by which
you would mean there would be some
documents which are documents like a PDF
file or an excel file or txt file images
of employees and other files which are
actually stored in the binary format
into into your database table if you
would so I have also a uploaded a video
on our how to do it from the sequel and
I mean in terms where you are where you
have a video how to how to insert delete
and update such files in your database
how to how to interact with them and a
fruity sequel queries and this is again
atl part i mean there there you need to
load it load such files into your system
the airmen are dbms stable systems with
the help of a size package so let's see
what we have here today i have created a
demo table okay
the table is something like this it's
called load blob which has an identity
column ID stored part and document so
let me create this table first
ok i'll let you know or each each of
them so first of all stored part will be
a column would be storing the part of
their you are very very the files are
actually deciding from that I mean they
have been loaded so what I have here in
my c drive is there are a B and C 3
folders first we look at a and B folders
a folder has a simple dessert it's JP
its image file and next same as with the
be i mean in this folder we have another
file and then we have a master text
which is with the name of file path or
txt this file is actually holding the
details if you would the directory part
of the images which V are about to load
through your ETL into your sequel server
table
I mean the table that we have just
created the load blog table so we would
be loading with the help of this and
through our import transformation so
let's take a look how we do it
so first of all I have an empty package
you're after I've named import-export
transformation so first and foremost let
me create a data flow task pull out let
me navigate into it and the first thing
is the flying source and this file
source would be you're the master file
in the flat flat file me just name it
flat file connection and let me browse
to my five-part txt
perfect and in the advanced I just need
this column as
file paths so
this is a preview if you would in the
column preview so okay we're through
with this so we have a column the name
five-part provides fine with us the next
and the important part which i wanted to
show you is the actual transformation
bros down to your transformation and you
would be seeing seeing us an import
column transformation cloud and now we
will be configuring an import column
transformation first moved onto your
import columns and check your file part
this is going to be the part where you
will be i mean which is the master
apart from where you will which which
will be giving the directory part of the
files which are which are required to be
loaded the next and the important step
is the important in input and output
properties here what you would see is in
the input call input column him you will
in the import and see the file part
after you have checked in your import
column five-part column it will be shown
in your butt here the next and the
important part is to import the column
output so here in the output column you
have to add a new column year
you have to add a column name it i just
put a name document perfect now for this
document field you would be seeing an ID
this is your ID take a lot of it is
before that also you would be seeing
that data type of the files that you are
trying to upload I mean the kind of a
document you would be uploading so since
we are loading image so that's good for
us take a note of the idea that you have
here in the document and the next thing
what you have to do is go to your import
column input go to the file part and in
the bottom at the bottom of it you will
be having file data column ID map the ID
to that of your document ID it is ready
for in this case so if you see this ID
should be mapped with your file part
file data column ID sounds good so
that's good with a configuration for us
so that's it now the destination again
so we are going to load it on the table
load blog so let me quickly just
configure this one for you
ok and my there and we loaded into low
deductible perfect the mappings here
ignore the idea since it's an identity
column if you want to store the part
from there the data was loaded
it's the final part for you if you do
not want you can just ignore it can
choose to ignore that
so for that i'm being I'll and unloading
the pot as well from there are i am
loading the file and the document is the
actual file which is going to be loaded
in this video process so we're through
with this transformation configuration
of the transformation
let's just run this package and see if
our data gets loaded so we have been
able to let me just show you right so if
you see you have the ID the files file
part and the and the document since this
is this is actually in the binary format
the data has got loaded in the binary
format so how do we verify that the data
was actually loaded if you remember I
mean we can verify it in the reverse of
a that is the export column which we
would be looking at in our next video or
demonstration what is export column or
transformation in a society but for the
time being let me just go back to a
couple of men come in my last video that
i had taught this to tease equal so okay
40 sequel verification this is first you
have to enable your oily or automation
procedures so for this database enable
that next I have actually a c4 love
which is an empty one
I just want to verify my files if they
were loaded correctly or not right
next I'm just pulling out the file for
ID 1 if you see or I'll e1 and i'm
naming the file with new pic nor jpg and
it's it'll be getting loaded on the
output part of see
florida sounds good so these are this is
the total peace equal which will
actually extract the file from your
database and put it in the part that we
have specified here this is the output
part if you see here this is the output
but for more details how to do with this
equal i have a video there please
therefore prefer that so timing let me
just pull out for you right
and let me just blows yeah if you see in
the C Drive you have the picture
retrieve so that was a that got stored
and it's good for us next
let me just pull out for ID to as well
and named it as a pic to write I have
the next one as well
perfect so friends that was that was a
quick and a brief demo on waters and
import column transformation and sis
stay tuned for the next export column
which is the reverse one which pulls out
the data from documents from database
and and exposure to certain as file
parts for you so thanks so much friends
have a good day