SSIS and Excel 2007

Question

SSIS and Excel 2007

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

guruprasat85 SSC Enthusiast Points: 136 More actions · Answer 1

thanks Dinesh but in Sql Server 2005 its easy too.. All you have to do is to use a data flow task and you are on!! 😀

mcloney SSC Eights! Points: 885 More actions · Answer 2

bilx10 (10/26/2008)
I know my data very well and also that ADO in excel doesn't handle columns with mixed data. A cell with numeric data will be taken as numeric and any data with alphanumeric characters ignored even if the destination is varchar datatype.

Hmmm, I don't think I've seen this, but I'm definitely going to watch for it now.

bilx10 (10/26/2008)
Of course if the excel files where properly created with each column formatted to the right datatype and data validated and verified there would be no problem with ADO (its an ADO problem not BIDS) but if you have been able to get the business units providing the excel files to do that please let us in on the secret of how thats done - most of the business users creating these sources don't have a clue what a datatype is and have no interest in learning. That includes their Management so there is no support for improvement.

I completely agree with you on that. Sigh 🙁 I think as DBAs we all try to impart the importance of following basic business rules to management, but I think as part of life, we're all disappointed when those rules are consistently broken with no consequences to anyone but us (and the users of the system who depend on timely and accurate data).

Cheers,

-m

danschl SSCertifiable Points: 5770 More actions · Answer 3

I agree with david the importing from excel is bad at best

Pretty bad considering they are both microsoft products

I always go to csv files to import

Although MS screwed that up also with sql2005

I had several CSV imports that worked sql2000 but failed in sql2005 and sql 2008

Gosta Munktell SSCertifiable Points: 6004 More actions · Answer 4

As I allready have stated I have abandoned Excel native format import because of the

problems.

But I became curios , will it work like described in the article which started this

thread. I have one machine with XP and VB2005, SQLserver2005 and Office2007 updated

I followed the procedure in the article but always get the encouring message:

Test connection failed because of an error in initializing provider. Invalid UDL file.

To my knowledge I have not done anything wrong but who knows?

Google did not give any strait answer so I gave up.

You fellows have you seen any pitchfalls like what I get?

//Gosta

DavidSimpson Hall of Fame Points: 3562 More actions · Answer 5

I followed the procedure in the article but always get the encouring message:
Test connection failed because of an error in initializing provider. Invalid UDL file.

I tried this as well and get the same error message as Gosta.

David

James Raddock Mr or Mrs. 500 Points: 569 More actions · Answer 6

Convert to CSV and import. Importing from excel has too many inherent problems. Back in 2002 I wrote a custom parser that had to handle this exact problem because even after hacking the jet drivers registry settings Excel still had problems. Just write it to a text file....It's very easy to write a simple .NET app to convert csvs to text automatedly.

Ravi Kumar V Mr or Mrs. 500 Points: 513 More actions · Answer 7

Ravi Kumar V

Mr or Mrs. 500

Points: 513

November 5, 2008 at 11:07 pm

#894697

GOOD ONE !!! 🙂

Alan_C SSC Enthusiast Points: 121 More actions · Answer 8

guruprasat85 (10/26/2008)
thanks Dinesh but in Sql Server 2005 its easy too.. All you have to do is to use a data flow task and you are on!! 😀

Can you elaborate please? I thought what Dinesh described was a data transformation task. You mean there is an simpler more intuitive way? It's not obvious to me.

darshan28 Ten Centuries Points: 1021 More actions · Answer 9

darshan28

Ten Centuries

Points: 1021

August 24, 2009 at 9:45 pm

#1043973

me too getting same error any solution plz

richardbartonbrown SSC Veteran Points: 241 More actions · Answer 10

This is a year later, but maybe somebody finds their way here and encounters the "Test connection failed because of an error in initializing provider. Invalid UDL file." or similar UDL error. I got this and investigated other how-to-define-SSIS-Excel articles. This one

http://blog.tylerholmes.com/2007/12/walkthrough-connecting-to-excel-2007.html

mentioned that you must install "Microsoft Office 12.0 Access Database Engine OLE DB Provider" and gave this MS link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=7554F536-8C28-4598-9B72-EF94E038C891&displaylang=en

When I install the MS component and follow the steps in the first link -- they are slightly different from this SQLServerCentral article -- the UDL errors go away.

If an additional download is indeed the fix, then that sucks -- MS gives us the option of picking a Connection Manager OLE DB Provider that is not loaded on the system...ugh.

wildh SSCarpal Tunnel Points: 4370 More actions · Answer 11

wildh

SSCarpal Tunnel

Points: 4370

October 28, 2010 at 3:31 am

#1243408

Thanks for the post. It's just helped me out.

Amy.G SSCrazy Points: 2305 More actions · Answer 12

Why couldn't I found this two weeks ago when I was nearly in tears over this?! OMG thank you for this.

Amy