|
|
|
Grasshopper
 
Group: General Forum Members
Last Login: Friday, May 22, 2009 10:12 AM
Points: 13,
Visits: 25
|
|
Hi
I wants to import Excel having Multiple Sheet using Excel Source in SSIS package in to a single table in single execution. how it is possible pls any one help me
Thanks
Anoop
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Thursday, July 02, 2009 3:40 PM
Points: 408,
Visits: 421
|
|
Use SSIS.
The excel data source allows you to connect to the workbook.
The Excel data source in the data flow task will allow you to specify which worksheet you wish to take your data from.
It work quite well as long as you sheet are well formatted.
Alan
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Thursday, July 02, 2009 3:40 PM
Points: 408,
Visits: 421
|
|
Alan (12/8/2008)
Use SSIS.
The excel data source allows you to connect to the workbook.
The Excel data source in the data flow task will allow you to specify which worksheet you wish to take your data from.
Sorry typed too fast the brain
1. The Excel connection manager allow you to connect to the workbook.
2. The Excel data source in the data flow task will allow you to specify which worksheet you wish to take your data from.
Alan
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 1:06 PM
Points: 2,696,
Visits: 6,582
|
|
not sure with SSIS, as there's wizards to help you, but from a pure TSQL point of view,
with multiple sheets, you'll need to add the excel as a linked server.
once it's linked, you can refer to each sheet as a table.
to do it in a single operation, it'd be something like
select * from excelalias...Sheet1$ UNION
select * from excelalias...Sheet2$
etc
here's an example of adding a linked server:
EXEC sp_addlinkedserver AK,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\EXCELALIAS_sumfy07.xls',
NULL,
'Excel 5.0;'
GO
--see the availalble tables int he spreadsheet
EXEC sp_tables_ex @table_server = 'EXCELALIAS'
--EXEC sp_dropserver EXCELALIAS
You can then select from the Excel table using SQL code as is needed do an insert into a sql table.
--sheet names end in a dollar sign..so if you name the sheet "Invoice", you access it as "Invoice$"
SELECT * FROM EXCELALIAS...SUMFY07$
SELECT * FROM EXCELALIAS...Sheet1$
SELECT * FROM OPENQUERY(EXCELALIAS, 'SELECT * FROM [Sheet1$]')
Lowell
help us help you! if your post doesnt have CREATE TABLE YOURTABLENAME and INSERT INTO YOURTABLE SQL statements, chances are you didn't post enough information for us to help you! see the link here to see how to post your data
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Friday, June 12, 2009 1:28 PM
Points: 21,
Visits: 54
|
|
Possible via the use of couple of "ForEachLopp" container(Ofcourse one can skin a cat in more then one way).
PreReq: Soon after the package gets create make sure the package level property "DelayValidation" is set to True. This is key to avoid any un-necessary errors being diaplyed due to invalid connections. Please remember most of the connections defined derieve their connectionstrings during execution.
1. define a .Net for oleDB provider connection (under connection manager)
2. The outer loop container reads the individual XLS file (use For EachFile Enumerator)
3. Define a package level variable to read the XLS file name (under variable mappings for the first container).
4. The connection defined in step 1 above, needs to have its "connectionstring" dynamically configured to append the value from variable in step 3 (this variable would hold the name of XLS file being processed currently), Index = 0
4. Now add a 2nd ForEachloop Container inside the 1st one. Use "Foreach ADO.NET Schema Rowset Enumerator", and at the same time point its connection to the one defined in step 1, and also change the schema property to "Tables"
5. Define another variable to save the name(s) of the individual workbooks for the XLS being processed.
6. Under variable mapping for the 2nd ForEach Loop, add new mapping and Assign this variable (Index = 2)
7. Once you know which workbook is under process, you could either write some custom .Net code (script task) or dataFlow control.
I hope its clear. Happy coding :)
|
|
|
|
|
Grasshopper
Group: General Forum Members
Last Login: Friday, June 12, 2009 1:16 PM
Points: 11,
Visits: 37
|
|
would you happen to have any help on writing this script?
im clueless about vb .net
and really need to pass the sheetnames into my dataflow task
|
|
|
|
