.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Looping through Excel 2007 tables in SSIS?

Posted By:      Posted Date: September 01, 2010    Points: 0   Category :Sql Server
Saw the (http://msdn.microsoft.com/en-us/library/ms345182(v=SQL.90).aspx) msdn article on MSDN, however I need to loop through Excel 2007 tables, which is not supported via the .Net Connection.  Is there a way to loop through the 2007 sheets without resorting to a script component?Steve Walker

View Complete Post

More Related Resource Links

Text truncated to 255 using SSIS ACE.oledbprovider to import excel 2007 spreadsheet

Hi All, Trying to use SSIS SQL 2005 to import an Excel 2007 file (.xlsx). I'm using Office 12.0 Access Database Engine OLEDB Provider with extended properties Excel 12.0;HDR=Yes;IMEX=1 The problem is when I have text with more than 255 characters. It basically defaults the source output to Unicode string[DT_WSTR] 255, If I change it it errors out. I have done the registry change according to http://msdn.microsoft.com/en-us/library/ms141683.aspx but I think it only applies to Excel source not OleDb Source. (A Excel source does give me a text instead of string )   I have also tried to increasing the length of output such as 4000 instead of 255 but it doesn't allow either.   Anybody can help?I'm stuck for a day. Thank you very much for your help. Regards, Koala  

SSIS - reading blank rows from Excel 2007 which are actually deleted

Folks, We are facing a strange issue with SSIS and thought I would share the same with you and see if there are any solutions. We are developing a solution where users upload their data in the form of Excel 2007 spreadsheets to a web application interface and then, the file is loaded into an Oracle RDBMS using SSIS 2008 (SSIS called using SSIS Object Model from .NET). Now, there are specific templates provided to the users for their input files and they are using the same. However, it is being observed that when the user deletes certain rows from Excel manually and tries to load it, the file load fails. The reason for the same   is seen when the package is run in debug mode – the excel source is reading a few rows (we are assuming the recently deleted rows) as NULL values in all the columns. Now, some searching revealed at http://www.tech-recipes.com/rx/2189/excel_2007_eliminate_blank_rows/ that the following manual set of operations are resolving the problem – 1. Go to the Ribbon, and select the Home tab. 2. In the Editing section, click the small arrow next to Find & Select . 3. Select Go To Special . 4. Select the Blanks radio button and click OK. 5. All blank columns/rows will be highlighted. 6. Delete However, this requires a user intervention and obviously, the users are not really loving it J . I just wanted to check that if there are any po

SSIS 2005 - Export Data into an Excel 2007 Table


Hello !

I have an excel file (xlsx) containing a table :

Excel Table

Once I launched my ssis task (successfully) to insert data in it, it is actually append after the table :

Excel Table after the SSIS task

So I am looking for a way to insert into the table and expand it with the data. I hope someone could help me.

Thank you !

SSIS package to import excel 2007 with different column numbers



I have an already developed SSIS 2005 package which can import maximum of 50 columns of *.xlsx file. The file format is same except with the column numbers. I have many components in SSIS package (e.g. unpivot data, derrived column etc) which are developed for 50 columns. The package works fine for importing 50 columns. But sometimes it fails for excel sheet having 12 columns (columns < 50) it gives error for the source columns cannot be found. Surprisingly, if the contents are copied to another file it may work but not surely. Please let me know if I can do anything in existing package.

Thanks in advance,

Dynamic PAckage to import multiple excel 2007 files in SSIS 2005


I want to import multiple excel 2007 files into Sql Server Database using SSIS 2005.

Can someone explain me the steps as i am new to SSIS.

While Importing Excel 2007 file to Datatable - headerrow problem


Hi there,


I am trying to simply extract an excel data from an uploaded file an put it into a datatable. In this case the excel file has 3 rows but when I fill the datatable I only see row count of 2.

I tried changing HDR:NO; to HDR:YES and vice versa, but no luck. 

What am I doing wrong? (Note: the excel file cannot have a  headerrow)


string connstr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pFilePath + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR:NO;\"";
            OleDbConnection conn = new OleDbConnection(connstr);
            DataTable dtTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            string strTablename = dtTables.Rows[0]["TABLE_NAME"].ToString();
            string strSQL = "SELECT * FROM [" + strTablename + "]";

            OleDbCommand cmd = new OleDbCommand(strSQL, conn);

            DataTable dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            //At this point row count=2 which doesn't make sense




Excel 2007




I want to develop an application which supports Server Side Excel Automation using a template(xltx). I am able to acheive most of the automation except(using excelpackage.dll - OfficeOpenXml), i am stuck up identifying the checkbox controls in my excel work sheet.

Any help on this is really appreciated.


My sample code.




display data in excel 2007


 I am using .net  version 1.1  and  excel 2003 to display data.I need to display data in 2007 .Can  anyone suggest the reference to be added ,connection string change and what should be imported.  

Importing Excel 2007 spreadsheet into WSS 3.0 -- Error Message



I'm trying to import (Custom Lists >> Import Spreadsheet) into WSS 3.0 and I'm getting the following message: 

Refers to the _layouts

You are not authorized to view this page.  You might not have permissions to view this direcotyr or page using the credentials you supplied. [More stuff here.]

Http ERror 403 - Forbidden

Is this just a permissions problem or is there some other underlying issue?  Should you be able to upload an Excel spreadsheet (with links) into a Custom List?


Thanks! Patti N.

In sharepoint excel working fine in office 2007 but not in office 2010


Hi All,

we receive error  while trying to create excel sheet using the template we have uploaded on the SharePoint 2007.


This occurs on a machine that has Office 2010 installed. Earlier the machine had Office 2007 and everything was working fine.


Can you let me know why is problem is coming and how to resolve this.



Sanjeev Tiwari


Sharepoint 2007 and Excel 2010 features


Does Sharepoint 2007 Server support the new Excel 2010 feaures like slicers? Or do i have to have Sharepoint 2010 to get that working (considering all other components besides Sharepoint 2010 are available in my Sharepoint 2007 environment) ?

- Thanks



Video: Connecting PivotTables in Excel 2007 to Data Stored in SharePoint Lists

Learn how to use the Open XML SDK 2.0 to create Excel 2007 PivotTables that are connected to data stored in SharePoint lists.

Interacting with the Excel Web Services API for SharePoint Server 2007

Get a quick start with the Excel Web Services API, which enables interaction with published Excel 2007 workbooks in SharePoint Server 2007 from a remote application. Learn considerations around session state, security, and performance.

Publishing Excel 2007 Workbooks to SharePoint Server 2007 (Visual How To)

Watch the video and explore code as you learn how to publish Excel 2007 Workbooks to SharePoint Server 2007 programmatically.

Sample: Publishing Excel 2007 Workbooks to SharePoint Server 2007

Explore the code in this visual how-to article as you learn how to publish Excel 2007 Workbooks to SharePoint Server 2007 programmatically.

Exporting MOSS 2007 List to Excel 2007

When doing the export, the columns "Item Type" and "Path" are automatically displayed in the sheet.  Is there anyway to suppress these fields from exporting automatically?  I'm sure this has already been asked before, but I couldn't find anything after much searching (perhaps I'm using the wrong keywords).  Any help would be greatly appreciated.

Linked Server to access Excel 2007



I'm tried SELECT * INTO XXX FROM OPENROWSET alongwith Microsoft.ACE.OLEDB.12.0.

Apparently the query requires the sql account to have SYSADMIN privileges.

Considering that SYSADMIN should not be provided to a database account on a Production Server, I tried using the Linked Server method.

Following is my code.

Exec sp_addlinkedserver 'AB2','Ace 12.0','Microsoft.ACE.OLEDB.12.0','\\\sagarr\Test1\cpc\c2\AB2.xlsx',NULL,'Excel 12.0;IMEX=1'
Exec sp_addlinkedsrvlogin 'AB2','false',NULL,NULL,NULL
Exec sp_dropserver 'AB2','droplogins'

Now i get the following error

Error.15247-User does not have permission to perform this action

My Excel file, Database and Windows Application run on separate machines.

i have provided the following privileges


EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'ad hoc distributed queries', 1

The DisAllowAdHocProcess in
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend