[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql 2005: ssis error reporting on issues with an excel document

Posted on 2011-10-24
3
Medium Priority
?
328 Views
Last Modified: 2013-11-10
hi all

i have developed an ssis application that loads information from an excel document, manipulates it in various ways and post the info to sql

it works perfectly - the person creating the excel document does not :(

each time they save the document they either add new columns, name columns differently, or save the data as a wrong data type, which is frustrating..

has anyone any tips on managing the spreadsheet so that i can alert them that the document is wrong - would you just advise on using error handling from ssis?

any help appreciated

cheers

mal

0
Comment
Question by:malraff
  • 2
3 Comments
 
LVL 21

Accepted Solution

by:
Jason Yousef, MS earned 2000 total points
ID: 37018347
Hi,
Dealing with different column names or wrong data types could be avoided by reading the excel file using a script task dynamically...

http://msdn.microsoft.com/en-us/library/ms403358.aspx
http://www.databasejournal.com/features/mssql/article.php/3919016/Accessing-Excel-Via-ADONET-Using-SSIS-Script-Task.htm

But dealing with added columns, that I can't think of a way..

As you said you might alert them by doing your own error handling procedure, such as emailing them a specific e-mail alerting them when excel fails on specific errors.

http://consultingblogs.emc.com/jamiethomson/archive/2005/08/08/1969.aspx

And always advise them to use a template !
0
 

Author Comment

by:malraff
ID: 37075124
sorry for no feedback  - im only back in the office now !
0
 
LVL 21

Expert Comment

by:Jason Yousef, MS
ID: 37083931
Great, let me know if you still need any help :)
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question