how to dump multiple excel files into single sql server table?
i have multiple(say example 5 .xls files) @ particular location, , files having same number of columns same column_names too.... different records/data each of xls files.
at same time have same number of column same column_name sql server table.
so how can dump multiple xls files data single table @ single run?
i thinking of using with:-
one each loop container , 1 dft (having 1 excel source , sql server destination)
can me approach?
thanks
regarsd,
kumar
what need steps
3- script task (scr) make connection string
4- copy sheet names of current excel file object variable
5 flc ( loop number2) loop through sheet object variables
you can check
http://dougbert.com/blogs/dougbert/archive/2008/06/18/excel-in-integration-services-part-2-of-3-tables-and-data-types.aspx
to list of excel sheets can use scripts , prefer because have more control , can find more ways if google.
in details have use .......
3-333333333333333333333333333333333333333333333333333333333333333333333333333333333333
3-333333333333333333333333333333333333333333333333333333333333333333333333333333333333
3-333333333333333333333333333333333333333333333333333333333333333333333333333333333333
3-333333333333333333333333333333333333333333333333333333333333333333333333333333333333
public sub main()
try
'' uvar_sourceconstr_connectionstring
' uvar_sourceconstr_provider, uvar_sourceconstr_datasource, uvar_sourceconstr_persistsecurityinfo, uvar_sourceconstr_extendedproperties, uvar_exceltabobjectname
dim connectionstring string
connectionstring = connectionstring & "provider=" & dts.variables("uvar_sourceconstr_provider").value.tostring
connectionstring = connectionstring & "data source=" & dts.variables("uvar_sourceconstr_datasource").value.tostring
connectionstring = connectionstring & ";"
if (dts.variables("uvar_sourceconstr_persistsecurityinfo").value.tostring.length > 0) then
connectionstring = connectionstring & "persist security info=" & dts.variables("uvar_sourceconstr_persistsecurityinfo").value.tostring
end if
if (dts.variables("uvar_sourceconstr_extendedproperties").value.tostring.length > 0) then
connectionstring = connectionstring & "extended properties="
connectionstring = connectionstring & """"
connectionstring = connectionstring & dts.variables("uvar_sourceconstr_extendedproperties").value.tostring
connectionstring = connectionstring & """"
end if
dts.variables("uvar_sourceconstr_connectionstring").value = connectionstring
dts.taskresult = dts.results.success
catch ex exception
dim strex string
strex = ex.message.tostring
end try
end sub
4-444444444444444444444444444444444444444444444444444444444444444444444444444
4-444444444444444444444444444444444444444444444444444444444444444444444444444
4-444444444444444444444444444444444444444444444444444444444444444444444444444
4-444444444444444444444444444444444444444444444444444444444444444444444444444
' variable in ssis uvar_exceltab objectname this is object variable in ssis
'
' remember must add system.xml in refrence
' find press ctrl+alt+j.
'
imports system
imports system.data
imports system.data.oledb
imports system.math
imports microsoft.sqlserver.dts.runtime
public class scriptmain
' execution engine calls method when task executes.
' access object model, use dts object. connections, variables, events,
' , logging features available static members of dts class.
' before returning method, set value of dts.taskresult indicate success or failure.
'
' open code , text editor help, press f1.
' open object browser, press ctrl+alt+j.
public sub main()
try
'
' add code here
'
'--------------------------------------------------------
dim excelfile string
dim connectionstring string
dim excelconnection oledbconnection
dim tablesinfile datatable
dim tablecount integer = 0
dim tableinfile datarow
dim currenttable string
dim tableindex integer = 0
dim exceltables string()
dim loopfornumnberofrealtabs integer = 0
connectionstring = dts.variables("uvar_sourceconstr_connectionstring").value.tostring
excelconnection = new oledbconnection(connectionstring)
excelconnection.open()
tablesinfile = excelconnection.getschema("tables")
tablecount = tablesinfile.rows.count
each tableinfile in tablesinfile.rows
currenttable = tableinfile.item("table_name").tostring
'str = tableinfile.item("table_type").tostring
'str = tableinfile.item("table_schema").tostring
'str = tableinfile.item("table_catalog").tostring
currenttable = currenttable.replace("'", "")
if right(currenttable, 1) = "$" then
loopfornumnberofrealtabs += 1
redim preserve exceltables(loopfornumnberofrealtabs - 1)
exceltables(loopfornumnberofrealtabs - 1) = currenttable
end if
next
excelconnection.close()
excelconnection = nothing
dts.variables("uvar_exceltabobjectname").value = exceltables
dts.taskresult = dts.results.success
catch ex exception
dim strex string
strex = ex.message.tostring
dts.taskresult = dts.results.failure
end try
end sub
end class
5-5555555555555555555555555555555555555555555555555555555555555555555555555555555555555555
5-5555555555555555555555555555555555555555555555555555555555555555555555555555555555555555
5-5555555555555555555555555555555555555555555555555555555555555555555555555555555555555555
5-5555555555555555555555555555555555555555555555555555555555555555555555555555555555555555
use loop ( second loop , set )
-in collection propertie set variable user::uvar_exceltab objectname object variable in ssis used in step 4
-variable mapping property = variable = uvar_excelactivetabname and index = 0
the uvar_excelactivetabname string ssis variable used in source ole object in ...
ole db source object --> connection manager --> dataaccess mode : table name or view name variable
ole db source object --> connection manager --> variable name : uvar_excelactivetabname
sincerely sh -- please kindly don’t forget mark post(s) answered question and/or vote post(s)
SQL Server > SQL Server Integration Services
Comments
Post a Comment