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

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

Error Handling loading Excel Files with SSIS 2005

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

I have an SSIS job which watches a directory for new files being submitted. On detecting a file(s) it determines the process to perform on it (based on the file name) and then reads in the file, loads it into the database, and deletes the file. All is fine. I want to add error handling though, specifically for the instance where the worksheet name does not correspond to that defined in the Excel Connection manager for this file.

At the moment if I change the sheet name to be different to that defined in the connection manager, the job fails and so (in turn) the whole process ceases (i.e. the for loop also goes to status failed).

I have tried event handler variables and checkpoints but without success. Could someone give me some advise, or point me to a article that would help?


View Complete Post

More Related Resource Links

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

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.

Excel Data Source SSIS AcquireConnection failed 0x80004005 unspecified error

Nuances of using Excel data sources with SSIS Your Development Machine Setup Needs Jet Drivers, available along with the default Office install so if you have Excel working fine locally, this is enough. Installing the "Office Data Connectivity Components" (download file name AccessDatabaseEngine.exe) as well as having Office installed corrupts the Jet Driver stack so that SSIS in VS 2008 cannot create the object to read the XLS file. You will see the title error "Unspecified Error" and "80004005" code. Resolution: Uninstall the Jet Engine pack on your development XP machine if you have Office. It will be listed as something like "Microsoft Access Database Engine (2010)". Run Office 200x setup with the Repair option and reboot. Try configuring the task in SSIS and it should be able to read the Sheet names again and columns.  Live Server (32-bit and 64-bit) Deploying your SSIS package to a server without Office 200x means it will not have the Jet Engine drivers to read XLS, XLSX files. You must install the 32-bit Office Data Connectivity Components - Jet Drivers mentioned above found here  (http://www.microsoft.com/downloads/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en). There are no 64-bit drivers; therefore regardless of 32 or 64-bit live environments, this step is the same. You shoul

ssis 2008 Loading MULTIPLE EXCEL file

HI it is possible for me to load multiple text file using for each loop,but if i try the same process for EXCEL file then my excel source throws an error. Also when i created my variable in value  i have given the path of my excel file ,still it didnt help me..because the moment i map the variable with the connection string of source excel connnection(property->expression->connection string) it start giving error if you could help me or provide some reference it will be of great help http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html i tried this link but got stuck with it on step 12 and 14 (below  is my connection string) and variable is @Filename which i have created Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\mushtaq.sheikh\Desktop\ForEach\New Microsoft Excel Worksheet1.xlsx;Extended Properties="Excel 12.0;HDR=YES"; The moment i try to edit my connection string by comparing connection string provided in link it gives error(an OLEBD error has occured with error code 0x80040E73) on excel source Thanks in advance

SSIS 2005 - XML Source no data loading to sql server database destination

Hello I am trying to load data from xml source to sql server destination, but the task executes with no data loaded to destination. i really appreciate any guidance in working and troubleshooting xml sources. Regards Harris

SSIS Web Service Task error : SQL 2005

On my dev machine every thing works, When i put my SSIS on QA server for testing, I get following error . Customer has only SQL 2005. Please provide any input on how to resolve this issue. I provided HTTP URL as .asmx?WSDL    already in config file.   Code: 0xC002F304    Source: Log Start Message to Process Manager Web Service Web Service Task    Description: An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The input Web Services Description Language (WSDL) file is not valid.    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTaskUtil..ctor(Object connection, String downloadedWSDL)    at Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebServiceTask.executeThread()". End Error DTExec: The package execution returned DTSER_FAILURE (1).Moonis Tahir MVP, MCPD, MCSD.net, MCTS BizTalk 2006/SQL 2005/SharePoint Server 2007 (Dev & Config)

Creating Multiple Excel files Using SSIS

Hello Friends,  I have to create multiple excel files(Per Year eg 2008 , 2009, 2010....) from datatable using SSIS  For that i have to use one template excel file that is already created on some location.  Any help would be appreciated.  Thanks Thanks

SQL 2005 Error - Connecting Excel workbook to SQL 2005 server

Question: I am trying to connect an Excel workbook to a remote SQL server database and getting the following error below (see code snippet). Any insight would be appreciated. Wondering if there is an easier method to connecting then what I have. I have VBA coding in the Excel workbook (if needed, I can provide it). Connection failed: SQLState: '01000' SQL Server Error: 10054 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead(recv()). Connection failed: SQLState: '08501' SQL Server Error: 11 [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network error. Check your network documentation.

XML for Analysis Parser; The restriction value - sql server 2005 with Excel 2010 error


Trying to refresh an existing pivot table that is connected to Analysis Services Sql Server 2005 database.  My excel 2010  gets the eroor:\

XML for Analysis parser:  The restriction value provided by the consumer either does not match other restrictions or refers to an unknown object.

I already tried the pivot table patch and I am still experiencing the error.  I also tried starting a new excel spreadshsst and connecting to two other sql servers of newer verions and was succesful.

SSIS Random Error, Ragged Right Flat Files


I am having a random error with my package(trying to generate fixed length text file using Ragged Right from one sql server table).

Data looks like for most of the runs(good data):


However, maybe for 1 out of 10-20 runs, some rows appear to be deleted misteriously(log shows everything fine and I am sure there is no other procoess update the file). This can happen anywhere(but just only at 1 place ). SSIS didn't provide any error or warning, it even says it inserted correct number of rows in the log.

Sometimes data generated like this:

DTLA01                                             08320100201N000020.7400C


Have anyone seen this kind of error before. I have tried e

Excel error 64-bit version of SSIS

I have a 64bit system and installed ssis on my system.
How do I changed the ssis project to 32 bit. I have this error:

Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.

Error: 0xC00F9304 at Package, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

Error: 0xC020801C at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component "Excel Source" (1) failed validation and returned error code 0xC020801C.


Error opening excel files stored in SharePoint 3.0 from VS2010 (TFS2010 setup)



First I hope this is the correct forum for te below question, and if not, please excuse and if possible divert me to the correct place.

We have a TFS2010 setup with (what I managed to identify) Sharepoint Services 3.0.
We have 2 users working in the same collection, and also share some of the projects in it with the below problems:

1. User A has Office 2007. She is unable to "edit workbook" on an excel file directly from VS2010, and gets an error of 'Unknown error trying to lock file'. I searched this around and didn't find a solution, but did find a workaround - If she checks out the file directly from the SharePoint site, she is able to edit the file, and in fact is able to later "edit workbook" directly from VS2010 (as long as she once checked it out through SharePoint).

2. User B has Office 2010. When he tries to "edit workbook", he gets an error of 'A problem occurred while connecting to the server. If the problem continues, contact your administrator'. When he goes directly to the SharePoint, he is able to check out the file, open it in Excel 2010, but is not able to check it back in (gets an error that the file cannot be located or so, I don't have the exact error). This means that this user cannot work with the SharePpoint stored files at all, and currently works on local copies on hi

How to generate an excel and send it as email attachment in SSIS 2005 Script Task Component?


I am using a "Script Task" component. I am trying with following three simple steps:

1) Obtain the excel content in HTML

Dim reportInHtml As String = GenerateHTMLReport()

2) Generate an excel with below code:

Dim message As MailMessage

message = New MailMessage(fromAddress, toAddress, subject, String.Empty)

Using memoryStream As MemoryStream = New MemoryStream()

        Dim contentAsBytes As Byte() = Encoding.UTF8.GetBytes(reportInHtml)
        memoryStream.Write(contentAsBytes, 0, contentAsBytes.Length)
        memoryStream.Seek(0, SeekOrigin.Begin)

        Dim contentType As ContentType = New ContentType()
        contentType.MediaType = "application/ms-excel"
        contentType.Name = "Excel_Report.xls"

        Dim excelAttachment

When dynamically creating an excel file, can SSIS create xlsx files successfully? I can only get SSI


Problem: An Excel file (XLSX) dynamically created by a SQL Task cannot be opened via Excel. The task succeeds, but when you try to open the workbook in Excel you get the fillowing error message: Excel cannot open the file 'myfile.xlsx" because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

1. If the Excel file (xlsx) is created manually in Excel, then the SQL Task has no problem creating table (new worksheets) in the xlsx (so the "CREATE TABLE" statement works fine)
2. If I adjust the connection and task to work with Excel 97-2003 (xls) format then the approach works perfectly
3. The execution results show success of the SQL Task, but there are two warning messages. These same warnings appear when the SQL Task creates a new table (worksheet) successfully in a workbook (xlsx) which was created manually in Excel (and is not corrupt). Messages as follows:
Warning: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
Warning: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

SSIS Details:
1. ConnectionString: "Data Sour

SSIS Error handling



I have an ssis package to transfer data [Data flow task] from excel to sql server, for error handling this job, i have created an database table to save the error, can you please tell me how to get the system error and save it to database table i have created .




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 which reads excel files fails on SQL Server Agent



I have a SSIS package which reads an excel file and loads data into a table using script component(C#) as a source. The package runs without any errors when I manually run it on my machine and on the server. But the package fails when run as a SQL Server Agent job.

I tried all the possible fixes I found on the web but still can't get it to work.

Could you shed some light on it?



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