SSIS and Excel 2007

  • guruprasat85

    SSC Enthusiast

    Points: 136

    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: 825

    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

    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: 5973

    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: 3552

    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: 539

    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

    GOOD ONE !!! 🙂

  • Alan_C

    SSC Enthusiast

    Points: 111

    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

    me too getting same error any solution plz

  • richardbartonbrown

    SSC Enthusiast

    Points: 131

    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: 4310

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

  • Amy.G

    SSCrazy

    Points: 2305

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

    Amy

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

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