question

venkatreddy avatar image
venkatreddy asked

how to load the data from excel sheet.

I have to load the data from an excel file containing 3 sheets in that file to sqlserver database table.Im loading data using **excel file source** to **oledb destination**.While running this package im able to load the data in single excel sheet in the excel file.How to resolve the issue.Thank you all.
sql-server-2008ssisadministration
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

salum avatar image
salum answered
Please check this article you will get the idea - [How to: Loop through Excel Files and Tables by Using a Foreach Loop Container][1] [1]: http://technet.microsoft.com/en-us/library/ms345182.aspx
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

venkatreddy avatar image venkatreddy commented ·
for multiple iterations using foreach loop container im able to access only first sheet of the each excel file.Does it works fine if i can place a script task to access each sheet of the excel sheet? thank you.
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
0 Likes 0 ·
Pratikc5 avatar image
Pratikc5 answered
> May be this code will help you. enter code here string xConnStr = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=D:\\Example.xlsx; Extended Properties='Excel 12.0; HDR=YES;'"; using (OleDbConnection connection = new OleDbConnection(xConnStr)) { OleDbCommand command = new OleDbCommand("Select * FROM [SHEETNAME$]", connection); connection.Open(); // Create DbDataReader to Data Worksheet using (DbDataReader dr = command.ExecuteReader()) { // SQL Server Connection String string sqlConnectionString = ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString.ToString(); // Bulk Copy to SQL Server using (SqlConnection destinationConnection = new SqlConnection(sqlConnectionString)) { destinationConnection.Open(); string t = destinationConnection.ConnectionTimeout.ToString(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection)) { bulkCopy.BulkCopyTimeout=300; bulkCopy.DestinationTableName = "Destination_table"; bulkCopy.WriteToServer(dr); } destinationConnection.Close(); dr.Close(); } }
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

venkatreddy avatar image venkatreddy commented ·
+1,yes its working thanku
0 Likes 0 ·
tommy29 avatar image
tommy29 answered
You may do this action with the help of how to repair ms excel file [ http://www.repairexcel2010.excelrepairtoolbox.com][1] works with any source of input information, including removable drives, network disks and flash memory [1]: http://www.repairexcel2010.excelrepairtoolbox.com
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.