.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

SSIS package to import excel 2007 with different column numbers

Posted By:      Posted Date: October 20, 2010    Points: 0   Category :Sql Server


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,

View Complete Post

More Related Resource Links

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.

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 import - Missing 1st column from Excel


Hi ,

I have gone through some of the materials posted on this topic. but didnot work out for me. I am trying to import the excel file using ssis. I have got the header details , I have to skip the header  while importing. I am using following sql commad: to import . I am able to skip the header row, but missing the 1st coumn from excel.I am not able to add the F1 column in the query . Can any know why it is not possible, because of this I am miisng the 1st column to import.

 SELECT F2, F3,F4, F5
from [Sheet1$]
where (f2is not null) and (f2<>'status')

Please help me out




Can not get excel connection when calling an SSIS package from ASP

I have an SSIS package importing data from excel. I have coded it and deployed it on the sql 2008 server. I then execute it via a stored procedure with the following: select @cmd = 'C:\"Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /sq "HRIS"' select   @cmd = @cmd + ' /SET \Package.Variables[User::path].Properties[Value];"' + @path + '"' exec   @rtn = master..xp_cmdshell @cmd   This works fine when  i execute it on my local machine. But when I try to call this Stored proc from my asp app (classic asp, not .net) I get the error: Code: 0xC0202009 Source: HRIS Connection manager "SourceConnectionExcel" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error". End Error   Any help would be greatly apprecitated

Looping through Excel 2007 tables in SSIS?

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

Loading Decimal Number From Excel into SQL Server 2005 using SSIS Package

If you have an excel spreadsheet that displays a number .02 (because it is formated) but internally it is stored as .2654329 and you want to load .2654329 how can you do that in the SSIS package?  When we load the spreadsheet it is loading .02.  Is there some kind of option in the SSIS package to tell the package to load the internal number?lcerni

Trouble importing strange column format report from Excel 2007 to SQL 08

Hi, I'm not sure whether this belongs in this section or the SSIS one so hopefully I've got it right!  Hoping someone will be able to help with a problem I'm having importing a report with header and detail rows from our antiquated POS system into SQL 2008 tables.  The reports export in Excel 2007 format and for each header row there can be one or more detail rows starting from column B.  As such I've tried several things such as openrowset, an Access 12.0 OLE DB source in SSIS and even COM automation of Excel to try and write a conditional split which will hold the header details in variables then write them to rows alongside the detail rows.  I've pasted a small sample of the data here as I wasn't able to attach it: 01/07/2010 @ 11:18 Page: 1 RECEIVING: Voucher Journal Sort: VC|Str|Vou Date|Vou #|Document SID Filter: Voucher Date: 01/06/2010@12:00a..30/06/2010@11:59p Include Item detail: DCS|Item#|Desc1|Attr|Material|Size|Qty VC Str Vou Date Vou # Qty ABC 001 15/06/2010 12345 38 E NN 66 200148 XXXXXXXX RED SHAD E NN 66 200149 YYYYYYYYY BLACK GO E PP 60 200154 ZZZZZZZZZ BLACK CDE 002 16/06/2010 13839 8 F SA 35 217500 XXXXXXXX Natural F SA FL 218674 YYYYYYYYY Chalk F SA WE 221462 ZZZZZZZZZ White FGH 001 21/06/2010 13905 3 F SH 85 126260 XXXXXXXX Navy IJK 001 23/06/2010 13914 3 E AA 61 250005 YYYYYY

How to create a SSIS package to import records form SQl Server 2008 tables to text files

I am a newbie to SSIS and would like to create a package to accomplish the task referenced in the above title. Will appreciate any links and pointers in the right direction.   Thanksakoranteng

if ssis package writes to excel file that might be open by a user

I have written a package that writes to an excel file to a network folder. Eventually this package will run as a job. What is the best way to handle the situation where someone opens the file or inadvertantly leaving it open while the package is running and and stop the package.? No one needs to modify or write anything to this file after it is finished

import multiple Excel 2007 files using openrowset

hello, I Have a folder that includes multiple excle files (ver 2007), i am trying  to loop through each file using vbscript to import its data to sql 2000 using openrowset but i am getting this error in the openrowset line "[Microsoft][ODBC sql server driver] [sql server] [OLE/DB provider returned message: the microsoft office access DB engine cannot open or write to the file '', it is already open exclusively by another user or you need permission to view or write its data] code 80040E14 source microsoft OLEDB provider for ODBC drivers Any help please    

Uploading Excel 2007 table into WSS 3.0 - How does SharePoint decide the column that is used to edit

Hi, In Excel 2007, there's an option to upload (export) an Excel table into SharePoint.  The range of cells have to be identified as a table.  In the Table ribbon, there's an Export button.  When I upload the Excel 2007 table, SharePoint (WSS 3.0) decides which column in the table will be linked to the properties dialog box for each line in the table.  It seems to be a sporadic decision on the part of SharePoint. Does anyone know how WSS 3.0 decides which column will be linked to properties?  And, is there any way to control this when uploading the file? Thanks!Thanks! Patti N.

SSIS Excel import skip first rows

Hello,1. Is it possible during import data from Excel to DB table skip first 6 rows for example?2. Also Excel data divided by sections with headers. Is it possible for example to skip every 12th row?Thank you,V. A.

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 Import Package Error - DTSRuntimeWrap 0xC0010026

Hi, I have a problem with importing dtsx package on SSIS 2008. When I try to import this in SSIS MSDB, I have an Exception from HRESULT: 0xC0010026 (Microsoft.SqlServer.DTSRuntimeWrap). The related topic does not help me, because the package is generated in 2008 version, imported with 2008 Management Studio. Any idea? Thanks a lot, Giovanni

SSIS 2008 - Excel Source using SQL Connection cancels as soon as package runs.

Let me preface this by saying I'm brand-new to SSIS development. I'm using 64-bit SSIS 2008 and I'm trying to read an Excel spreadsheet using OLE DB. I've already run into and corrected the Run64BitRuntime problem.  Now when I invoke the Package it cancels immediately with the following output: SSIS package "Lesson1X.dtsx" starting. Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning. SSIS package "Lesson1X.dtsx" finished: Canceled. The program '[5400] Lesson1X.dtsx: DTS' has exited with code 0 (0x0). When I edit the Excel Source component itself there are no errors and I am able to Preview the results successfully.  If I use Table or View to access the file, the package runs successfully. Any suggestions will be greatly appreciated. Thanks

Create Excel file dynamically in SSIS package and fill it by resultset data returned by Stored proce


Hello Every One,

I have a scenario in which i am executing a Stored Procedure which returned a result set.

I want to create a Excel file [name_date_time] dynamically, and fill result set data into Excel file. And then finally sent Excel file to Specific user[Email ID].

Point is that the excel file should have unique name so i want to give name as name_date_time.

So every time when package run new file is created with unique name.


Can any one suggest me who to create Excel file and fill result set data into Excel file.

And then finally send that Excel file ?







SSIS cannot find excel columns when using SQL command to import

I'm trying to import an Excel file, but receive the following error during the process:

Validation is completed
[DTS.Pipeline] Information: Prepare for Execute phase is beginning.
Progress: Prepare for Execute - 0 percent complete
Progress: Prepare for Execute - 33 percent complete
Progress: Prepare for Execute - 66 percent complete
Progress: Prepare for Execute - 100 percent complete
[DTS.Pipeline] Information: Pre-Execute phase is beginning.
Progress: Pre-Execute - 0 percent complete
[Source - Individuals$ [1]] Error: Column "Last Name 2" cannot be found at the datasource.
[DTS.Pipeline] Error: component "Source - Individuals$" (1) failed the pre-execute phase and returned error code 0xC0202005.

My Excel Source is using "SQL command" data access mode:
Select * from [Members$]

Is there something that I'm missing when using the 'SQL command' mode? Thanks in advance for any help.

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