How to
import excel with multiple sheets to the database using SSIS
Introduction
Generally
we have task likr import the excel data to sal data table .. ??? we will use
the ssis tool to overcome these tasks we know . if the same task we make some
thing completex like single excel file having the multiple sheets
(sheet1,steet2 ……) all the sheets table data need to import to database table
Look here
the process
1. First
create one excel file and name it as “Sampledata excel” and save it as 97-2003
excel file formate because my system have that version support for the reason .
2.creathe
data like
SNo
|
Name
|
Salary
|
1
|
Krishn
|
10000
|
And select
the data – got to insert tab – select the table – one popup will come juct
click ok – and save – like that create all the sheets with different data to
chek
SNo
|
Name
|
Salary
|
2
|
Krishn1
|
20000
|
SNo
|
Name
|
Salary
|
3
|
Krishn2
|
30000
|
3. Create
one SSIS project and add new ssis package
Go to
Control flow tab – right click here and select variables – add one variable and
name it as “VariableTableName” – select data
type as string – value is sheet name “Sheet1$”(don’t forgot to put he $ symbole
to the value ).
4.In
control flow tab drag and drop the For Each loop and – right click on the for
each loop and click edit – in that go to collocation tab – select “For Each
ADO.NET Schema ROOT Set ” in the enumerator – in enumerator
configuration select connection as new connection – one popup will open – in
that click new again one more popup will open – in that select the provider as
–
“Microsoft Jet 4.0 OLE DB Provider” click ok – and
browse excel .xls file – and go to ALL tab – in Advanced properties “Extended Provider as Excel 8.0” text the connection ok
.
Select
schema type as table
Drag
on data flow in for each loop – double
click on the data flow it will redirect to the Data flow tab
In that –
drag 2 tools Source as Excel , Destination as OLEDB
Browse the
excel sheet and select the data access mode to “table name or view name ” it
will populate the variables name which we created before
No comments:
Post a Comment